Analysis on the storage format of Oracle nested tables

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.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian