Analysis on the storage format of Oracle nested tables
Oracle nested tables are rarely used. The following describes how to store them. For example, a user corresponds 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 (100) 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, 'developer group'), t_dept (200, 'developer group ')));
SQL> insert into t_user values (2, 'lily', t_dept_arry (t_dept (300, 'design a group'), t_dept (400, 'Design two group ')));
SQL> commit;
-- To traverse all the department information of a user, you must use the table format.
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 Zhang San 100 develop a group
1 zhangsan 200 developer Group 2
2. Design a group by Li Si 300
2 Li Si 400 Design Group 2
Dump block:
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]
00 01 00 00 00 00 01 00 00 00 00 0a e4 23 00 37 09 00 00 00 00 00 00 27 00
00 00 00 00 01 88 01 27 01 01 00 02 0f 84 01 0f 02 c2 02 08 bf aa b7 a2 d2
Bb d7 e9 0f 84 01 0f 02 c2 03 08 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]
00 01 00 00 00 00 01 00 00 00 00 0a e4 24 00 37 09 00 00 00 00 00 00 27 00
00 00 00 00 01 88 01 27 01 01 00 02 0f 84 01 0f 02 c2 04 08 c9 e8 bc c6 d2
Bb d7 e9 0f 84 01 0f 02 c2 05 08 c9 e8 bc c6 b6 fe d7 e9
End_of_block_dump
Insert into t_user values (3, 'wang wu', 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]
00 01 00 00 00 00 01 00 00 00 00 0a e4 49 00 21 09 00 00 00 00 00 00 11 00
00 00 00 00 01 88 01 11 01 01 01 01 09 84 01 09 02 c2 06 02 bc d2
End_of_block_dump
-- After reading the above dump results, I am confused and don't know how to store them. However, I found a clue in user_objects that 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 $
-- Test what is the difference with a common clob?
Drop table test purge;
Create table test
(
Id number,
T_clob clob
);
Insert into test values (1, 'developer group 2 group ');
Insert into test values (1, 'Design one group design two group ');
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]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4a 00 20 09 00 00
00 00 00 00 10 00 00 00 00 01 5f 00 53 d1 4e 00 7e c4 5f 00 53 d1 4e 8c
7e c4
LOB
Locator:
Length: 84 (52)
Version: 1
Byte Length: 2
LobID: 00.00.002.161.00.00.002.16a.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 (shocould 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]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4b 00 20 09 00 00
00 00 00 00 10 00 00 00 00 00 01 8b be 8b a1 4e 00 7e c4 8b be 8b a1 4e 8c
7e c4
LOB
Locator:
Length: 84 (52)
Version: 1
Byte Length: 2
LobID: 00.00.002.161.00.00.002.16a.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 (shocould 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]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4c 00 12 09 00 00
00 00 00 00 02 00 00 00 00 01 5b b6
LOB
Locator:
Length: 84 (38)
Version: 1
Byte Length: 2
LobID: 00.00.0020.1.00.00.0020.a.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 (shocould be '0x00 ')
Blocks: 0
Bytes: 2
Version: 00000.0000000001
Inline data [2]
Select the dump content twice, but you still cannot determine whether the two are equivalent:
00 01 00 00 00 00 01 00 00 00 00 0a e4 49 00 21 09 00 00 00 00 00 00 11 00
00 00 00 00 01 88 01 11 01 01 01 01 09 84 01 09 02 c2 06 02 bc d2
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4c 00 12 09 00 00
00 00 00 00 02 00 00 00 00 01 5b b6
Summary: nesting stores nested content through lob. TOM said that as a storage mechanism, he prefers to create parent and child tables and create another view, like a real nested table, a nested table is suitable for programming.