[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.

时间： 2024-10-01 15:13:45

[20140114]简单探究nested table.txt的相关文章

[20140114]简单探究nested table-之2.txt

  [20140114]简单探究nested table-存储问题.txt 对nested table一点也不熟悉,估计也很少人使用,今天看了一些文档,做一些简单的探究,估计以后也不会使用. 1.测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Re

[20170910]imp参数buffer的简单探究2.txt

[20170910]imp参数buffer的简单探究2.txt --//exp,imp已经很少在用,如果存在8i估计还会用一下,下面因为别人遇到升级忘记家buffer参数(8i),导致导入缓慢, --//当然还有许多因素,比如存在lob字段,不过还是简单探究参数buffer. --//上个星期做了参数buffer的简单探究,发现缓存大小除以记录的最大长度=每次插入记录数量. --//忘记测试缺省是多少,补充测试看看. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRI

[20170908]imp参数buffer的简单探究.txt

[20170908]imp参数buffer的简单探究.txt --//exp,imp已经很少在用,如果存在8i估计还会用一下,下面因为别人遇到升级忘记家buffer参数(8i),导致导入缓慢, --//当然还有许多因素,比如存在lob字段,不过还是简单探究参数buffer. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER -----------------------------

[20130307]clob字段的简单探究2.txt

[20130307]clob字段的简单探究2.txt 前段时间研究一下clob字段.看看函数empty_clob()与null的区别.今天测试有信息插入的情况. http://space.itpub.net/267265/viewspace-755269参考文档: http://www.juliandyke.com/Presentations/LOBInternals.ppt 从上次的测试可以看出:1.clob字段NULL与empty_clob()是不同的.2.如果clob字段有信息,除了保存信

[20131220]频率直方图的简单探究.txt

[20131220]频率直方图的简单探究.txt http://allthingsoracle.com/histograms-part-1-why/http://www.itpub.net/thread-1816475-1-1.html 昨天本想看12c的混合直方图的相关信息,无意之中发现以上链接,Jonathan Lewis给出很好的例子,newkid的翻译写的很清晰,自己再按照上面的介绍写一些例子做一些测试. 我的测试环境11.2.0.3,建立测试环境: SYS@test> @verBANN

[20171109]简单探究文件ab_ASM_SID.dat.txt

[20171109]简单探究文件ab_ASM_SID.dat.txt --//昨天看了一篇Solving Communication problems between DB and ASM instances.链接 --//http://www.toadworld.com/platforms/oracle/b/weblog/archive/2017/11/07/solving-communication-problems-between-db-and-asm-instances --//如果意外

[20170315]简单探究dg的mrp进程.txt

[20170315]简单探究dg的mrp进程.txt --//昨天上午在做测试做在线日志与备用日志大小不一样时遇到一个令我困惑的问题,链接: http://blog.itpub.net/267265/viewspace-2135377/ --//发现mrp进程并没有打开数据文件的句柄.这样MRP进程如何应用日志的呢?简单探究看看: 1.环境: SYS@192.168.31.8/xxxxxx> @ &r/ver1 PORT_STRING                    VERSION  

[20160902]简单探究linux的free命令.txt

[20160902]linux命令free的buffers与cached表示什么.txt --简单探究linux的free命令. $ free              total       used       free     shared    buffers     cached Mem:     132261196   29620656  102640540          0     174340   24209712 -/+ buffers/cache:    5236604 

[20141106]type and table.txt

[20141106]type and table.txt --看看建立type与表. SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Li