[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$正好对上,两者关联获得完成的记录信息.