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

 

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

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

2.今天看一下存储结构:
SCOTT@test> select rowid,dept_emp.deptno,dname,loc,SYS_NC0000400005$ from dept_emp;
ROWID                  DEPTNO DNAME          LOC           SYS_NC0000400005$
------------------ ---------- -------------- ------------- --------------------------------
AABE2mAAEAAAAJGAAA         10 ACCOUNTING     NEW YORK      EFEA01D4944B26C4E0432864A8C0DE07
AABE2mAAEAAAAJGAAB         20 RESEARCH       DALLAS        EFEA01D4944C26C4E0432864A8C0DE07
AABE2mAAEAAAAJGAAC         30 SALES          CHICAGO       EFEA01D4944D26C4E0432864A8C0DE07
AABE2mAAEAAAAJGAAD         40 OPERATIONS     BOSTON        EFEA01D4944E26C4E0432864A8C0DE07

SCOTT@test>

SCOTT@test> @lookup_rowid AABE2mAAEAAAAJGAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    282022          4        582          0 4,582                alter system dump datafile 4 block 582 ;

--使用bbed观察:
BBED> set dba 4,582
        DBA             0x01000246 (16777798 4,582)

BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8028     0x2c

BBED> x /rnccx
rowdata[0]                                  @8028
----------
flag@8028: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8029: 0x01
cols@8030:    4

col    0[2] @8031: 10
col   1[10] @8034: ACCOUNTING
col    2[8] @8045: NEW YORK
col   3[16] @8054:  0xef  0xea  0x01  0xd4  0x94  0x4b  0x26  0xc4  0xe0  0x43  0x28  0x64  0xa8  0xc0  0xde  0x07

--看看nested table的存储:
SCOTT@test> select * from dba_extents where owner=user and segment_name='EMPS_NT';
OWNER  SEGMENT_NAME  PARTITION_NAME SEGMENT_TYPE       TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ ------------- -------------- ------------------ ---------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  EMPS_NT                      NESTED TABLE       USERS                     0          4        536      65536          8            4

SCOTT@test> select owner,segment_name,header_file,header_block from dba_segments where owner=user and segment_name='EMPS_NT';
OWNER  SEGMENT_NAME         HEADER_FILE HEADER_BLOCK
------ -------------------- ----------- ------------
SCOTT  EMPS_NT                        4          538

--使用bbed从4,538往下看:

BBED> set dba 4,542
        DBA             0x0100021e (16777758 4,542)

BBED> map /v
File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
Block: 542                                   Dba:0x0100021e
------------------------------------------------------------
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                      @0
    ub1 type_kcbh                           @0
    ub1 frmt_kcbh                           @1
    ub1 spare1_kcbh                         @2
    ub1 spare2_kcbh                         @3
    ub4 rdba_kcbh                           @4
    ub4 bas_kcbh                            @8
    ub2 wrp_kcbh                            @12
    ub1 seq_kcbh                            @14
    ub1 flg_kcbh                            @15
    ub2 chkval_kcbh                         @16
    ub2 spare3_kcbh                         @18

struct ktbbh, 72 bytes                     @20
    ub1 ktbbhtyp                            @20
    union ktbbhsid, 4 bytes                 @24
    struct ktbbhcsc, 8 bytes                @28
    sb2 ktbbhict                            @36
    ub1 ktbbhflg                            @38
    ub1 ktbbhfsl                            @39
    ub4 ktbbhfnx                            @40
    struct ktbbhitl[2], 48 bytes            @44

struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

sb2 kdbr[14]                               @118

ub1 freespace[7289]                        @146

ub1 rowdata[753]                           @7435

ub4 tailchk                                @8188
--很明显当前信息保存在4,542.sb2 kdbr[14] @118,正好14条.

BBED> x /rxnccntnnn
rowdata[596]                                @8031
------------
flag@8031: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8032: 0x01
cols@8033:    7

col   0[16] @8034:  0xef  0xea  0x01  0xd4  0x94  0x4b  0x26  0xc4  0xe0  0x43  0x28  0x64  0xa8  0xc0  0xde  0x07
col    1[3] @8051: 7782
col    2[5] @8055: CLARK
col    3[7] @8061: MANAGER
col    4[3] @8069: 7839
col    5[7] @8073: 1981-06-09 00:00:00
col    6[3] @8081: 2450

--col0就是NESTED_TABLE_ID,与前面的隐含字段SYS_NC0000400005$正好对上,两者关联获得完成的记录信息.

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

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