《Expert one on one Oracle》- 表- 笔记-4

嵌套表

类似于父/子表，但是有较大的差别。其中一个就是，在嵌套表中，父表中有多少行，就有多少嵌套表。使用嵌套表的两种方法：PL/SQL以及存储机制。
创建，查询，更新嵌套表的例子：

SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.

SQL> create or replace type emp_tab_type
2 as table of emp_type
3 /
Type created.

SQL> 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;
Table created.

SQL> alter table emps_nt add constraint emps_empno_unique
2 unique(empno)
3 /
Table altered.

SQL> insert into dept_and_emp--插入嵌套表
2 select dept.*,
3 CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm
4 from emp
5 where emp.deptno = dept.deptno ) AS emp_tab_type )
6 from dept
7 /
5 rows created.
--multiset告诉Oracle子查询可能返回多行
--CAST告诉Oracle将返回结果作为一个集合

SQL> insert into table--插入嵌套子表
2 ( select emps from dept_and_emp where deptno = 10 )
3 values
4 ( 1234, 'NewEmp', 'CLERK', 7782, sysdate, 1200, null );
1 row created.

SQL> delete from table--删除嵌套子表
2 ( select emps from dept_and_emp where deptno = 20 )
3 where ename = 'SCOTT';
1 row deleted.

SQL> select d.dname, e.empno, ename--查询嵌套子表
2 from dept_and_emp d, table(d.emps) e
3 where d.deptno in ( 20 );

DNAME EMPNO ENAME
-------------- ---------- ----------
RESEARCH 7369 SMITH
RESEARCH 7566 JONES
RESEARCH 7876 ADAMS
RESEARCH 7902 FORD


SQL> update--更新嵌套子表
2 table( select emps
3 from dept_and_emp
4 where deptno = 10
5 )
6 set comm = 100
7 /
3 rows updated.

SQL> update
2 table( select emps
3 from dept_and_emp
4 where deptno = 1
5 )
6 set comm = 100
7 /
update
*
ERROR at line 1:
ORA-22908: reference to NULL table value

通过exp/imp可以查看到嵌套表的创建语句。通过运行如下的语句，可以进一步得到：

SQL> SELECT SQL_TEXT FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE
2 '%EMP%';
……
SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID,SYS_NC_ROWINFO$ FROM "SCOTT"."EMPS_NT"

--执行上述查询出来的SQL语句
SQL> SELECT /*+NESTED_TABLE_GET_REFS+*/ NESTED_TABLE_ID,SYS_NC_ROWINFO$
2 from
3 "SCOTT"."EMPS_NT";

--得到表EMPS_NT中两列的值
NESTED_TABLE_ID
--------------------------------
SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
--------------------------------------------------------------------------------

A704A65FA4D14709807E86B8741D5ED7
EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, 100)

A704A65FA4D14709807E86B8741D5ED7
EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '17-NOV-81', 5000, 100)

A704A65FA4D14709807E86B8741D5ED7
EMP_TYPE(7934, 'MILLER', 'CLERK', 7782, '23-JAN-82', 1300, 100)

实际上，这两列NESTED_TABLE_ID，SYS_NC_ROWINFO$被隐藏。NESTED_TABLE_ID列是父表实际的外健，SYS_NC_ROWINFO$列，让ORACLE将一列看作一个对象。通过这种方式，ORACLE通过系统产生的主健和外健实现父子表。可以查看系统为父表创建的主健：

SQL> select name
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = 'DEPT_AND_EMP')
6 /

NAME
------------------------------
DEPTNO
DNAME
LOC
EMPS
SYS_NC0000400005$

系统为父表创建了一列SYS_NC0000400005$，为子表的外健，通过如下查询可以确定:

SQL> select SYS_NC0000400005$ from dept_and_emp WHERE SYS_NC0000400005$ = 'A704A
65FA4D14709807E86B8741D5ED7';

SYS_NC0000400005$
--------------------------------
A704A65FA4D14709807E86B8741D5ED7

从而通过NESTED_TABLE_GET_REFS可以单独查询子表，例如：
SQL> select /*+ nested_table_get_refs */ empno, ename from emps_nt where ename l
ike '%AR%';

EMPNO ENAME
---------- ----------
7782 CLARK
7521 WARD
7654 MARTIN


通过如上查询结果，嵌套表的物理结构为：
如果EXP/IMP嵌套表，那么可以发现嵌套表的定义中有RETURN AS VALUE因子，这表示会将潜套表行全部返回给应用的客户端。可以改写为RETURN AS LOCATOR，那么只是将指针方会给应用的客户端。

同时，从上面的例子可以看到，嵌套子表系统创建的主健实际上是没有索引的，而在访问这个子表的时候，ORACLE总是需要关联这个字段，所以最好为这个字段创建索引。更进一步，最好的方式将嵌套子表建立成索引组指表。

临时表

创建临时表时并不分配存储参数。当运行的时候，一个SESSION第一次将数据存入临时表，一个临时段将会创建。每个SESSION都有自己的临时段。

ORACLE的临时表应该预先创建了，只是通常情况下它们都没有数据，直到SESSION存入数据。有两种类型的临时表，基于SESSION的/基于事务的。

create global temporary table temp_table_session
on commit preserve rows--on commit delete rows
as
select * from scott.emp where 1=0
/

临时表的限制：
a. 不能定义参考完整性约束
b. 不能有VARRAY和NESTED TABLE类型的列
c. 不能是IOT/分区表/索引（HASH）CLUSTER表
d. 不能通过ANALYZE产生统计信息

针对最后一条，如果需要临时表在运行的时候有参考的统计信息，可以采用如下的方式：
a. 创建与临时表一样的普通表（表名，数据量都相同），创建索引
b. 对表和索引进行分析
c. 将统计信息导出

begin
dbms_stats.create_stat_table( ownname => USER,
stattab => 'STATS');

dbms_stats.export_table_stats( ownname => USER,
tabname => 'TEMP_ALL_OBJECTS',
stattab => 'STATS');

dbms_stats.export_index_stats( ownname => USER,
indname => 'TEMP_ALL_OBJECTS_IDX',
stattab => 'STATS');
end;
/

d. 删除普通表，创建临时表
e. 导入步骤c中导出的统计信息

begin
dbms_stats.import_table_stats( ownname => USER,
tabname => 'TEMP_ALL_OBJECTS',
stattab => 'STATS');
dbms_stats.import_index_stats( ownname => USER,
indname => 'TEMP_ALL_OBJECTS_IDX',
stattab => 'STATSʹ');
end;
/

对象表

对象表和嵌入表类似，ORACLE为了维持对象表，在普通表上增加了许多隐藏的东西。其实，其最终还是转换成关系型数据库的行和列。所以，对于物理存储的表而言，倾向于不使用对象表，因为不会知道背后隐藏的机制究竟会产生什么样的副作用。当然在存储过程，程序开发中使用是一个不错的选择。例如，用对象视图来同步对象与数据库数据。

SQL> create table people_tab
2 ( name varchar2(30) primary key,
3 dob date,
4 home_city varchar2(30),
5 home_street varchar2(30),
6 home_state varchar2(2),
7 home_zip number,
8 work_city varchar2(30),
9 work_street varchar2(30),
10 work_state varchar2(2),
11 work_zip number
12 )
13 /
Table created.

SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 state varchar2(2),
6 zip number
7 )
8 /
Type created.

SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created.

SQL> create view people of person_type
2 with object identifier (name)
3 as
4 select name,dob,
5 address_type(home_city,home_street,home_state,home_zip) home_a
6 address_type(work_city,work_street,work_state,work_zip) work_a
7 from people_tab
8 /
View created.

SQL> insert into people values ( 'Tom','15-mar-1965',
2 address_type( 'Reston','123 Main Street','Va','45678' ),
3 address_type( 'Redwood','1 Oracle Way','Ca','23456' ) );
1 row created.

SQL> select name, p.home_address.city from people p;
NAME HOME_ADDRESS.CITY
------------------------------ ------------------------------
Tom Reston

SQL> select name from people_tab;
NAME
------------------------------
Tom