Analysis of storage format of Oracle nested table

Oracle nested tables are rarely used, and the following is a look at how they are stored. With an example, a user is responding to a department.

Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

Sql> Create type T_dept as Object (

dept_id number,
Dept_name VARCHAR2 (20)
)
/
Sql> Create type T_dept_arry as Varray (+) of t_dept
/
Sql> CREATE TABLE T_user (
USER_ID number,
User_name VARCHAR2 (10),
Depts T_dept_arry
)
/
sql> INSERT INTO T_user values (1, ' Zhang San ', T_dept_arry (t_dept (100, ' develop a group '), T_dept (200, ' development Two group '));
sql> INSERT INTO T_user values (2, ' John Doe ', T_dept_arry (t_dept (300, ' Design a group '), T_dept (400, ' Design two group '));

Sql> commit;

-to traverse all of the user's departmental information, you need to use the special form of table

Sql> Select User_id,user_name,d.dept_id,d.dept_name from T_user u,table (u.depts) D;
USER_ID user_name dept_id Dept_name
---------- ---------- ---------- --------------------
1 sheets 3,100 developing a group
1 3,200 development two groups
2 Li 4,300 design a group
2 Li 4,400 design two group
dump block to see:
Block_row_dump:
tab 0, row 0, @0x3f47
tl:81 FB:--h-fl--lb:0x1 Cc:3
Col 0: [2] C1 02
Col 1: [4] d5 c5 C8 fd
Col 2: [69]
0a E4 23 00 37 09 00 00 00 00 00 00 27 00 (XX)
At 0f, 0f, C2 to a BF aa B7 A2 D2, XX
BB D7 E9 0f 0f C2 Geneva BF aa B7 A2 b6 fe d7 E9
tab 0, row 1, @0x3ef6
tl:81 FB:--h-fl--lb:0x1 Cc:3
Col 0: [2] C1 03
Col 1: [4] c0 ee CB C4
Col 2: [69]
0a E4 24 00 37 09 00 00 00 00 00 00 27 00 (XX)
In the xx-XX (0f) 0f C2 c9 E8 BC c6 D2
BB D7 E9 0f 0f C2 c9 E8 BC c6 b6 fe D7 E9
End_of_block_dump

INSERT into T_user values (3, ' Harry ', T_dept_arry (t_dept (500, ' home ')));
Commit

Block_row_dump:
tab 0, row 0, @0x3f5d
tl:59 FB:--h-fl--lb:0x1 Cc:3
Col 0: [2] C1 04
Col 1: [4] cd f5 ce e5
Col 2: [47]
0a E4 49 00 21 09 00 00 00 00 00 00 11 00 (XX)
At xx-----------
End_of_block_dump
--look at the result of the dump above, confused, do not know how to save, but found clues in user_objects, there is a LOB field
sql>Select Object_name,object_type from User_objects s;
object_name object_type
------------------------------ -------------------
sys_lob0000082685c00003$$ LOB
T_user TABLE
T_dept_arry TYPE
T_dept TYPE

sql>Select column_name, Segment_name, index_name
From User_lobs S
where s.table_name = ' t_user ';
Column_nam segment_name index_name
---------- ------------------------------ ------------------------------

Depts sys_lob0000082685c00003$$ sys_il0000082685c00003$$

--What's the difference between a test and a normal CLOB?

drop table test Purge;
CREATE TABLE Test
(
ID number,
T_clob CLOB
);
INSERT into test values (1, ' develop a group of development two groups ');
INSERT into test values (1, ' Design a group of design two groups ');
INSERT into test values (1, ' home ');

Commit

tab 0, row 0, @0x3f5d
tl:59 FB:--h-fl--lb:0x1 Cc:2
Col 0: [2] C1 02
Col 1: [52]
At 0c, the 0a E4 4a 00 20 09 00 00, at the same xx xx
XX, XX, 5f, D1 4e, 7e c4 5f xx d1 4e 8c
7e C4
Lob
Locator:
Length:84 (52)
Version:1
Byte Length:2
lobid:00.00.00.01.00.00.00.0a.e4.4a
flags[0x02 0x0c 0x80 0x00]:
Type:clob
Storage:basicfile
Enable Storage in Row
Characterset format:implicit
Partitioned Table:no
Options:varingwidthreadwrite
Inode:
Size:32
flag:0x09 [Valid Datainrow]
Future:0x00 (should be ' 0x00 ')
blocks:0
Bytes:16
version:00000.0000000001
Inline Data[16]
Dump of memory from 0x00002ae680c155ec to 0X00002AE680C155FC
2ae680c155e0 d153005f [_. S.]
2ae680c155f0 c47e004e d153005f c47e8c4e [N.~._. S.n.~.]
tab 0, row 1, @0x3f22
tl:59 FB:--h-fl--lb:0x1 Cc:2
Col 0: [2] C1 02
Col 1: [52]
At 0c, the 0a e4 4b 00 20 09 00 00, at the same xx xx
xx xx xx 8b be 8b A1 4e xx 7e C4 8b be 8b A1 4e 8c
7e C4
Lob
Locator:
Length:84 (52)
Version:1
Byte Length:2
Lobid:00.00.00.01.00.00.00.0a.e4.4b
flags[0x02 0x0c 0x80 0x00]:
Type:clob
Storage:basicfile
Enable Storage in Row
Characterset format:implicit
Partitioned Table:no
Options:varingwidthreadwrite
Inode:
Size:32
flag:0x09 [Valid Datainrow]
Future:0x00 (should be ' 0x00 ')
blocks:0
Bytes:16
version:00000.0000000001
Inline Data[16]
Dump of memory from 0X00002AE680C155B1 to 0X00002AE680C155C1
2ae680c155b0 8bbe8b01 7e004ea1 8bbe8bc4 7e8c4ea1 [...] N.~ ..... N.~]
2ae680c155c0 02012cc4 [.,..]
tab 0, row 2, @0x3ef5
TL:45 FB:--h-fl--lb:0x1 Cc:2
Col 0: [2] C1 02
Col 1: [38]
At 0c, the 0a E4 4c 00 12 09 00 00, at the same xx xx
At the xx xx, xx xx 5b b6
Lob
Locator:
Length:84 (38)
Version:1
Byte Length:2
lobid:00.00.00.01.00.00.00.0a.e4.4c
flags[0x02 0x0c 0x80 0x00]:
Type:clob
Storage:basicfile
Enable Storage in Row
Characterset format:implicit
Partitioned Table:no
Options:varingwidthreadwrite
Inode:
Size:18
flag:0x09 [Valid Datainrow]
Future:0x00 (should be ' 0x00 ')
blocks:0
Bytes:2
version:00000.0000000001
Inline Data[2]

Select the contents of the dump two times, but it is still not possible to determine whether the two are equivalent:

xx xx, 0a e449 00 21 09 00 00 00 00 XX
At xx-----------

At 0c, the 0a e4 4c 00 12 09 00 00, at the same xx xx
At the xx xx, xx xx 5b b6

Summary: Nesting is storing nested content through lobs, and Tom says that as a storage mechanism, he prefers to create parent-child tables, and can create a view that looks like a real nested table, and that nested tables are suitable for programming constructs.

Analysis of storage format of Oracle nested table