Nested Table 타입 칼럼을 가진 테이블을 생성하면 암시적으로 Nested Table과 Nested Table 인덱스가 생성됩니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
CREATE OR REPLACE TYPE tnt_number FORCE IS TABLE OF NUMBER;
/
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 tnt_number) NESTED TABLE c2 STORE AS t1_c2;
INSERT INTO t1 VALUES (1, tnt_number (1, 2));
COMMIT;
아래는 t1 테이블을 조회하는 쿼리의 실행 계획입니다. 스칼라 서브쿼리와 유사한 형태로 SYS_FK0000084463N00002$ 인덱스를 통해 T1_C2 테이블을 조회하는 것을 확인할 수 있습니다.
-- 2
SELECT * FROM t1;
------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1_C2 | 2 | 2 |
|* 2 | INDEX RANGE SCAN | SYS_FK0000084463N00002$ | 2 | 1 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 7 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NESTED_TABLE_ID"=:B1)
t1, t2_c1 테이블, SYS_FK0000084463N00002$ 인덱스 모두 일반 오브젝트입니다.
-- 3
SELECT object_name, object_type
FROM user_objects
WHERE object_name IN ('T1', 'T1_C2', 'SYS_FK0000084463N00002$');
OBJECT_NAME OBJECT_TYPE
----------------------- -----------
T1 TABLE
T1_C2 TABLE
SYS_FK0000084463N00002$ INDEX
3 행이 선택되었습니다.
*_TAB_COLS 뷰를 조회하면 RAW 타입의 SYS_NC0000200003$ 칼럼이 암시적으로 생성된 것을 확인할 수 있습니다. SYS_NC0000200003$ 값은 t1_c2 테이블의 nested_table_id 값과 연결됩니다.
-- 4-1
SELECT column_name, data_type, hidden_column, virtual_column, user_generated
FROM user_tab_cols
WHERE table_name = 'T1'
COLUMN_NAME DATA_TYPE HIDDEN_COLUMN VIRTUAL_COLUMN USER_GENERATED
----------------- ---------- ------------- -------------- --------------
C1 NUMBER NO NO YES
C2 TNT_NUMBER NO YES YES
SYS_NC0000200003$ RAW YES NO NO
3 행이 선택되었습니다.
아울러 *_NESTED_TABLES, *_NESTED_TABLE_COLS 뷰에서 Nested Table 정보와 Nested Table 칼럼 정보를 확인할 수 있습니다. t1_c2 테이블은 nested_table_id, column_value 칼럼을 가지고 있습니다.
-- 4-2
SELECT table_name, table_type_owner, table_type_name, parent_table_name, parent_table_column
FROM user_nested_tables
WHERE parent_table_name = 'T1';
TABLE_NAME TABLE_TYPE_OWNER TABLE_TYPE_NAME PARENT_TABLE_NAME PARENT_TABLE_COLUMN
---------- ----------------- --------------- ----------------- -------------------
T1_C2 TUNA TNT_NUMBER T1 C2
1개의 행이 선택되었습니다.
-- 4-3
SELECT column_name, data_type
FROM user_nested_table_cols
WHERE table_name = 'T1_C2';
COLUMN_NAME DATA_TYPE
--------------- ---------
NESTED_TABLE_ID RAW
COLUMN_VALUE NUMBER
2 행이 선택되었습니다.
SYS_FK0000084463N00002$ 인덱스를 t1_c2 테이블의 nested_table_id 칼럼으로 구성된 단일 칼럼 인덱스입니다.
-- 4-1
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE index_name LIKE 'SYS_FK0000084463N00002$';
INDEX_NAME INDEX_TYPE TABLE_NAME
----------------------- ---------- ----------
SYS_FK0000084463N00002$ NORMAL T1_C2
1개의 행이 선택되었습니다.
-- 4-2
SELECT column_name, column_position
FROM user_ind_columns
WHERE index_name = 'SYS_FK0000084463N00002$';
COLUMN_NAME COLUMN_POSITION
--------------- ---------------
NESTED_TABLE_ID 1
1개의 행이 선택되었습니다.
t1 테이블의 SYS_NC0000200003$ 칼럼에 저장된 nested_table_id 값으로 t1_c2 테이블을 조회하면 column_value 값을 확인할 수 있습니다.
-- 5-1
SELECT c1, c2, SYS_NC0000200003$ FROM t1;
C1 C2 SYS_NC0000200003$
-- ---------------- --------------------------------
1 TNT_NUMBER(1, 2) 5F51A81B8383450EB43B6CC059F7B6E9
1개의 행이 선택되었습니다.
-- 5-2
SELECT * FROM t1_c2;
ORA-22812: 내포된 테이블 열 저장 테이블을 참조할 수 없습니다
-- 5-3
SELECT /*+ NESTED_TABLE_GET_REFS */
column_value
, DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) AS fno
, DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) AS bno
FROM t1_c2
WHERE nested_table_id = '5F51A81B8383450EB43B6CC059F7B6E9';
COLUMN_VALUE FNO BNO
------------ --- ---
1 13 181
2 13 181
2 행이 선택되었습니다.
앞선 쿼리에 조회한 파일 번호와 블록 번호로 생성한 블록 덤프를 살펴보면 t1_c2 테이블에 2개의 로우가 저장된 것을 확인할 수 있습니다.
-- 6-1
ALTER SYSTEM DUMP DATAFILE 13 BLOCK 181;
-- 6-2
block_row_dump:
tab 0, row 0, @0x1f6a
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [16] 5f 51 a8 1b 83 83 45 0e b4 3b 6c c0 59 f7 b6 e9
col 1: [ 2] c1 02
tab 0, row 1, @0x1f81
tl: 23 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [16] 5f 51 a8 1b 83 83 45 0e b4 3b 6c c0 59 f7 b6 e9
col 1: [ 2] c1 03
end_of_block_dump