[20140114]简单探究nested table.txt

 

对nested table一点也不熟悉,估计也很少人使用,今天看了一些文档,做一些简单的探究,估计以后也不会使用.

1.测试环境:
SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create or replace type emp_type
as object
(empno number(4)
,ename varchar2(10)
,job varchar2(9)
,mgr number(4)
,hiredate date
,sal number(7,2)
,comm number(7,2)
);
/

create type emp_tab_type as table of emp_type;
/

create table dept_emp(deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13),
emps  emp_tab_type
) nested table emps store as emps_nt;

insert into dept_emp select dept.*,cast (multiset(select empno,ename,job,mgr,hiredate,sal,comm from emp
where emp.deptno= dept.deptno) as emp_tab_type) from dept;

2.查看数据:
select * from dept_emp;

SCOTT@test> column EMPS format a60
SCOTT@test> select * from dept_emp;
    DEPTNO DNAME          LOC           EMPS(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
---------- -------------- ------------- ------------------------------------------------------------
        10 ACCOUNTING     NEW YORK      EMP_TAB_TYPE(EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '1981-
                                        06-09 00:00:00', 2450, NULL), EMP_TYPE(7839, 'KING', 'PRESID
                                        ENT', NULL, '1981-11-17 00:00:00', 5000, NULL), EMP_TYPE(793
                                        4, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NUL
                                        L))

        20 RESEARCH       DALLAS        EMP_TAB_TYPE(EMP_TYPE(7369, 'SMITH', 'CLERK', 7902, '1980-12
                                        -17 00:00:00', 800, NULL), EMP_TYPE(7566, 'JONES', 'MANAGER'
                                        , 7839, '1981-04-02 00:00:00', 2975, NULL), EMP_TYPE(7788, '
                                        SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL),
                                         EMP_TYPE(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00
                                        ', 1100, NULL), EMP_TYPE(7902, 'FORD', 'ANALYST', 7566, '198
                                        1-12-03 00:00:00', 3000, NULL))

        30 SALES          CHICAGO       EMP_TAB_TYPE(EMP_TYPE(7499, 'ALLEN', 'SALESMAN', 7698, '1981
                                        -02-20 00:00:00', 1600, 300), EMP_TYPE(7521, 'WARD', 'SALESM
                                        AN', 7698, '1981-02-22 00:00:00', 1250, 500), EMP_TYPE(7654,
                                         'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 14
                                        00), EMP_TYPE(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00
                                        :00:00', 2850, NULL), EMP_TYPE(7844, 'TURNER', 'SALESMAN', 7
                                        698, '1981-09-08 00:00:00', 1500, 0), EMP_TYPE(7900, 'JAMES'
                                        , 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL))

        40 OPERATIONS     BOSTON        EMP_TAB_TYPE()

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0vz1wpt1ndrzj, child number 0
-------------------------------------
select * from dept_emp

Plan hash value: 537580636

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |        |     3 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS_NT                 |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | SYS_FK0000282022N00004$ |      1 |     1   (0)|
|   3 |  TABLE ACCESS FULL          | DEPT_EMP                |      4 |     3   (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NESTED_TABLE_ID"=:B1)

--可以发现执行的过程,先通过索引探查EMPS_NT表,条件是"NESTED_TABLE_ID"=:B1.
--这样说明存在一个隐含字段.

SELECT col#, segcol#, intcol#, name,type#
  FROM sys.col$
WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = USER AND object_name = 'DEPT_EMP');

      COL#    SEGCOL#    INTCOL# NAME                      TYPE#
---------- ---------- ---------- -------------------- ----------
         1          1          1 DEPTNO                        2
         2          2          2 DNAME                         1
         3          3          3 LOC                           1
         4          0          4 EMPS                        122
         4          4          5 SYS_NC0000400005$            23

SELECT col#, segcol#, intcol#, name,type#
  FROM sys.col$
WHERE obj# IN (SELECT object_id FROM dba_objects WHERE owner = USER AND object_name = 'EMPS_NT');

      COL#    SEGCOL#    INTCOL# NAME                      TYPE#
---------- ---------- ---------- -------------------- ----------
         0          1          1 NESTED_TABLE_ID              23
         0          0          2 SYS_NC_ROWINFO$             121
         1          2          3 EMPNO                         2
         2          3          4 ENAME                         1
         3          4          5 JOB                           1
         4          5          6 MGR                           2
         5          6          7 HIREDATE                     12
         6          7          8 SAL                           2
         7          8          9 COMM                          2

--从这些信息可以看出dept_emp,name=epms的 SEGCOL#=0,并没有保存在dept_emp的数据段里面,而name=SYS_NC0000400005$,type#=23
--而emps_NT的NESTED_TABLE_ID,type#=23,说明这个与dept_emp.SYS_NC0000400005$存在关联.

SCOTT@test> select SYS_NC0000400005$,deptno,dname,loc from dept_emp;
SYS_NC0000400005$                    DEPTNO DNAME          LOC
-------------------------------- ---------- -------------- -------------
EFEA01D4944B26C4E0432864A8C0DE07         10 ACCOUNTING     NEW YORK
EFEA01D4944C26C4E0432864A8C0DE07         20 RESEARCH       DALLAS
EFEA01D4944D26C4E0432864A8C0DE07         30 SALES          CHICAGO
EFEA01D4944E26C4E0432864A8C0DE07         40 OPERATIONS     BOSTON

SELECT a.deptno,A.dname,A.loc,a.SYS_NC0000400005$,
       b.NESTED_TABLE_ID,
       b.SYS_NC_ROWINFO$
  FROM dept_emp a, TABLE (a.emps) b;

    DEPTNO DNAME          LOC           SYS_NC0000400005$                NESTED_TABLE_ID                  SYS_NC_ROWINFO$(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)
---------- -------------- ------------- -------------------------------- -------------------------------- --------------------------------------------------------------------------------
        10 ACCOUNTING     NEW YORK      EFEA01D4944B26C4E0432864A8C0DE07 EFEA01D4944B26C4E0432864A8C0DE07 EMP_TYPE(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL)
        10 ACCOUNTING     NEW YORK      EFEA01D4944B26C4E0432864A8C0DE07 EFEA01D4944B26C4E0432864A8C0DE07 EMP_TYPE(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL)
        10 ACCOUNTING     NEW YORK      EFEA01D4944B26C4E0432864A8C0DE07 EFEA01D4944B26C4E0432864A8C0DE07 EMP_TYPE(7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL)
        20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07 EFEA01D4944C26C4E0432864A8C0DE07 EMP_TYPE(7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL)
        20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07 EFEA01D4944C26C4E0432864A8C0DE07 EMP_TYPE(7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL)
        20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07 EFEA01D4944C26C4E0432864A8C0DE07 EMP_TYPE(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000, NULL)
        20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07 EFEA01D4944C26C4E0432864A8C0DE07 EMP_TYPE(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100, NULL)
        20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07 EFEA01D4944C26C4E0432864A8C0DE07 EMP_TYPE(7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0)
        30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07 EFEA01D4944D26C4E0432864A8C0DE07 EMP_TYPE(7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL)

14 rows selected.

==SYS_NC0000400005$=NESTED_TABLE_ID,通过关联确定信息.

SCOTT@test> select b.NESTED_TABLE_ID,b.* from  dept_emp,TABLE (dept_emp.emps) B;
NESTED_TABLE_ID                       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM
-------------------------------- ---------- ---------- --------- ---------- ------------------- ---------- ----------
EFEA01D4944B26C4E0432864A8C0DE07       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450
EFEA01D4944B26C4E0432864A8C0DE07       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000
EFEA01D4944B26C4E0432864A8C0DE07       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300
EFEA01D4944C26C4E0432864A8C0DE07       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800
EFEA01D4944C26C4E0432864A8C0DE07       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975
EFEA01D4944C26C4E0432864A8C0DE07       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000
EFEA01D4944C26C4E0432864A8C0DE07       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100
EFEA01D4944C26C4E0432864A8C0DE07       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000
EFEA01D4944D26C4E0432864A8C0DE07       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300
EFEA01D4944D26C4E0432864A8C0DE07       7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500
EFEA01D4944D26C4E0432864A8C0DE07       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400
EFEA01D4944D26C4E0432864A8C0DE07       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850
EFEA01D4944D26C4E0432864A8C0DE07       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0
EFEA01D4944D26C4E0432864A8C0DE07       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950

14 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1070495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1070495/