数据库表：嵌套表与临时表的深入解析

# 数据库表：嵌套表与临时表的深入解析 ## 1. 嵌套表的操作与存储 ### 1.1 嵌套表的基本操作 嵌套表在数据库操作中有着独特的地位，但部分功能并未得到充分文档支持。例如，`NESTED_TABLE_GET_REFS` 功能仅在 `EXP` 和 `IMP` 操作中有特定用途，不建议用于生产代码。若需对嵌套表数据进行查询，推荐使用非嵌套方式，示例代码如下： ```sql EODA@ORA12CR1> select d.deptno, d.dname, emp.* 2 from dept_and_emp D, table(d.emps) emp 3 / ``` ### 1.2 嵌套表的存储结构 创建嵌套表时，会生成两个实际的表。以如下代码为例： ```sql EODA@ORA12CR1> create table dept_and_emp 2 (deptno number(2) primary key, 3 dname varchar2(14), 4 loc varchar2(13), 5 emps emp_tab_type 6 ) 7 nested table emps store as emps_nt; ``` 除了我们指定创建的表，还会额外创建一个隐藏列，并在该隐藏列上创建唯一约束。同时，会为嵌套表 `EMPS_NT` 创建两个隐藏列，其中 `SYS_NC_ROWINFO$` 是虚拟列，`NESTED_TABLE_ID` 是外键，可与父表关联，但该列缺少索引。 ### 1.3 嵌套表的返回方式 `RETURN AS VALUE` 子句用于描述嵌套表如何返回给客户端应用程序。默认情况下，Oracle 会按值将嵌套表返回给客户端，即每行都会传输实际数据。也可设置为 `RETURN AS LOCATOR`，客户端将获得数据指针，仅在解引用该指针时才会传输数据。例如，对于仅在用户双击部门时才显示员工信息的客户端应用程序，可考虑使用 `RETURN AS LOCATOR` 以减少网络往返。 ### 1.4 嵌套表的优化 为了优化嵌套表的性能，`NESTED_TABLE_ID` 列必须建立索引。更好的解决方案是使用索引组织表（IOT）存储嵌套表，它能将子行按 `NESTED_TABLE_ID` 物理存储在一起，减少物理 I/O，并消除 `RAW(16)` 列上的冗余索引。同时，可将 `EMPNO` 列的唯一和非空约束纳入 `CREATE TABLE` 命令。示例代码如下： ```sql EODA@ORA12CR1> CREATE TABLE "EODA"."DEPT_AND_EMP" 2 ("DEPTNO" NUMBER(2, 0), 3 "DNAME" VARCHAR2(14), 4 "LOC" VARCHAR2(13), 5 "EMPS" "EMP_TAB_TYPE") 6 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 7 TABLESPACE "USERS" 8 NESTED TABLE "EMPS" 9 STORE AS "EMPS_NT" 10 ((empno NOT NULL, unique (empno), primary key(nested_table_id,empno)) 11 organization index compress 1 ) 12 RETURN AS VALUE; ``` ### 1.5 嵌套表的使用建议 不建议将嵌套表作为永久存储机制，原因如下： - **存储开销**：父表和子表都会添加 `RAW(16)` 列，增加不必要的存储开销。 - **约束冗余**：父表通常已有唯一约束，额外的唯一约束会带来不必要的开销。 - **使用不便**：不使用不受支持的构造（如 `NESTED_TABLE_GET_REFS`）时，嵌套表难以单独使用，虽然可在查询时进行非嵌套操作，但无法进行大规模更新。 不过，嵌套表在编程构造和视图中非常有用。若要将其作为存储机制，建议使用 IOT 以避免索引开销。 ## 2. 临时表的特性与使用 ### 2.1 临时表的基本概念 临时表用于在事务或会话期间保存中间结果集。临时表中的数据仅对当前会话可见，即使当前会话提交数据，其他会话也无法看到。临时表不会产生多用户并发问题，即使对其进行“锁定”操作，也不会阻止其他会话使用自己的临时表。 从 Oracle 12c 开始，可通过将 `TEMP_UNDO_ENABLED` 参数设置为 `TRUE`，使临时表几乎不产生重做日志。临时表会从当前登录用户的临时表空间分配存储，若从定义者权限过程访问，则使用该过程所有者的临时表空间。 ### 2.2 临时表的类型 临时表可分为会话级和事务级两种类型： - **会话级临时表**：使用 `ON COMMIT PRESERVE ROWS` 子句创建，数据在会话期间保留，直到会话断开或手动删除。示例代码如下： ```sql EODA@ORA12CR1> create global temporary table temp_table_session 2 on commit preserve rows 3 as 4 select * from scott.emp where 1=0 5 / ``` - **事务级临时表**：使用 `ON COMMIT DELETE ROWS` 子句创建，数据在事务提交后消失。示例代码如下： ```sql EODA@ORA12CR1> create global temporary table temp_table_transaction 2 on commit delete rows 3 as 4 select * from scott.emp where 1=0 5 / ``` ### 2.3 临时表的使用示例 以下是一个展示两种临时表行为差异的示例： ```sql EODA@ORA12CR1> insert into temp_table_session select * from scott.emp; EODA@ORA12CR1> insert into te ```