TBL$OR$IDX$PART$NUM 함수는 파티션과 관련된 문서화되지 않은 함수로 파티션과 서브파티션의 포지션 또는 오브젝트 ID를 반환하며, 내부적으로 파티션 Pruning을 위해 사용됩니다.
TBL$OR$IDX$PART$NUM 함수의 구문은 아래와 같습니다.
- 첫 번째 파라미터는 파티션 테이블을 입력합니다.
- 두 번째 파라미터는 기본적으로 0을 입력하며, 글로벌 파티션 인덱스의 오브젝트 ID를 입력하면 인덱스에 대한 파티션 정보가 반환됩니다.
- 세 번째 파라미터는 반환 값을 결정합니다. 1은 파티션의 포지션, 0은 서브파티션의 포지션, 4는 파티션의 오브젝트 ID, 3은 서브파티션의 오브젝트 ID를 반환하며, 8을 Deffered Global 인덱스에 대한 파티션 Pruning에 사용하는 것으로 보입니다.
- 네 번째 파라미터는 기본적으로 0을 입력하며, 65535을 Partial 인덱스에 대한 파티션 Pruning에 사용하는 것으로 보입니다. 다섯 번째 파라미터는 ROWID 또는 파티션 키 값을 입력합니다.
TBL$OR$IDX$PART$NUM (
owner.table_name
, index_object_id -- 0: default
, d# -- 1: partition position, 0: subpartition position, 4: partition object id, 3: partition object id, 8: defferd global index
, p# -- 0: default, 65535: partial index
, { rowid | value [, value] }
);
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하겠습니다. t1_x1는 글로벌 파티션 인덱스, t1_x2는 비파티션 인덱스, t1_x3는 Partial 로컬 파티션 인덱스입니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2, c3, c4, c5)
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c2)
SUBPARTITION TEMPLATE (SUBPARTITION sp1, SUBPARTITION sp2) (
PARTITION p205001 VALUES LESS THAN (DATE '2050-02-01')
, PARTITION p205002 VALUES LESS THAN (DATE '2050-03-01')
, PARTITION p205003 VALUES LESS THAN (DATE '2050-04-01')
, PARTITION p205004 VALUES LESS THAN (DATE '2050-05-01')
)
AS
SELECT ADD_MONTHS (DATE '2050-01-01', CEIL (ROWNUM / 64) - 1)
, NVL (NULLIF (MOD (ROWNUM, 64), 0), 64)
, NVL (NULLIF (MOD (ROWNUM, 64), 0), 64)
, NVL (NULLIF (MOD (ROWNUM, 64), 0), 64)
, NVL (NULLIF (MOD (ROWNUM, 64), 0), 64)
FROM XMLTABLE ('1 to 256');
CREATE INDEX t1_x1 ON t1 (c3) GLOBAL PARTITION BY HASH (c3) (PARTITION p1, PARTITION p2);
CREATE INDEX t1_x2 ON t1 (c4);
CREATE INDEX t1_x3 ON t1 (c5) LOCAL INDEXING PARTIAL;
아래 2-1번 쿼리는 파티션과 서브파티션의 포지션, 2-2번 쿼리는 파티션과 서브파티션의 오브젝트 ID를 반환합니다.
-- 2-1: position
SELECT TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 1, 0, DATE '2050-02-01') AS partition_position
, TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 0, 0, DATE '2050-02-01', 1) AS subpartition_position
FROM DUAL;
PARTITION_POSITION SUBPARTITION_POSITION
------------------ ---------------------
2 4
1개의 행이 선택되었습니다.
-- 2-2: object_id
SELECT TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 4, 0, DATE '2050-02-01') AS partition_object_id
, TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 3, 0, DATE '2050-02-01', 1) AS subpartition_object_id
FROM DUAL;
PARTITION_OBJECT_ID SUBPARTITION_OBJECT_ID
------------------- ----------------------
235845 235851
1개의 행이 선택되었습니다.
아래와 같이 TBL$OR$IDX$PART$NUM 함수의 다섯 번째 파라미터에 값 대신 ROWID를 입력할 수 있습니다. WHERE 절에 TBL$OR$IDX$PART$NUM 함수를 사용하면 파티션을 Pruning할 수 있습니다.
-- 3-1: partition
SELECT *
FROM t1
WHERE TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 1, 0, ROWID) = 2;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 64 | 5 |
| 1 | PARTITION RANGE SINGLE| | 1 | 3 | 2 | 2 | 64 | 5 |
| 2 | PARTITION HASH ALL | | 1 | 3 | 1 | 2 | 64 | 5 |
| 3 | TABLE ACCESS FULL | T1 | 2 | 3 | 3 | 4 | 64 | 5 |
--------------------------------------------------------------------------------------------
-- 3-2: subpartition
SELECT *
FROM t1
WHERE TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 0, 0, ROWID) = 4;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 36 | 3 |
| 1 | PARTITION COMBINED ITERATOR| | 1 | 3 | KEY | KEY | 36 | 3 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 3 | 4 | 4 | 36 | 3 |
-------------------------------------------------------------------------------------------------
TBL$OR$IDX$PART$NUM 함수의 두 번째 파라미터에 글로벌 파티션 인덱스의 오브젝트 ID를 입력하면 인덱스의 파티션 정보가 반환됩니다.
-- 4-1
SELECT object_name, subobject_name, object_id
FROM user_objects
WHERE object_name = 'T1_X1';
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
----------- -------------- ---------
T1_X1 235856
T1_X1 P1 235857
T1_X1 P2 235858
3 행이 선택되었습니다.
-- 4-2
SELECT TBL$OR$IDX$PART$NUM (TUNA.T1, 235856, 1, 0, 1) AS partition_id
, TBL$OR$IDX$PART$NUM (TUNA.T1, 235856, 4, 0, 1) AS object_id
FROM DUAL;
PARTITION_ID OBJECT_ID
------------ ----------
2 235858
1개의 행이 선택되었습니다.
아래 쿼리는 Deffered Global 인덱스에 대한 파티션 Pruning을 수행합니다. Predicate Information 항목에서 TBL$OR$IDX$PART$NUM 함수의 두 번째 매개변수에 8이 사용된 것을 확인할 수 있습니다.
-- 5-1
ALTER TABLE t1 TRUNCATE PARTITION p205001 UPDATE INDEXES;
-- 5-2
SELECT * FROM t1 WHERE c4 = 1;
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3 | 5 |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1 | 1 | 3 | ROWID | ROWID | 3 | 5 |
|* 2 | INDEX RANGE SCAN | T1_X2 | 1 | 4 | | | 3 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C4"=1)
filter(TBL$OR$IDX$PART$NUM(<?>,0,8,0,"T1".ROWID)=1)
아래 쿼리는 Partial 인덱스에 대한 파티션 Pruning을 수행합니다. Predicate Information 항목에서 TBL$OR$IDX$PART$NUM 함수의 네 번째 매개변수에 65535이 사용된 것을 확인할 수 있습니다.
-- 6-1
ALTER TABLE t1 MODIFY SUBPARTITION p205001_sp1 INDEXING OFF;
-- 6-2
SELECT * FROM t1 WHERE c5 = 1;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3 | 12 |
| 1 | VIEW | VW_TE_2 | 1 | 2 | | | 3 | 12 |
| 2 | UNION-ALL | | 1 | | | | 3 | 12 |
| 3 | PARTITION COMBINED ITERATOR | | 1 | 1 | KEY | KEY | 3 | 11 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 7 | 1 | KEY | KEY | 3 | 11 |
|* 5 | INDEX RANGE SCAN | T1_X3 | 7 | 4 | KEY | KEY | 3 | 8 |
| 6 | PARTITION COMBINED ITERATOR | | 1 | 1 | KEY | KEY | 0 | 1 |
|* 7 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 | 1 | 0 | 1 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(((TBL$OR$IDX$PART$NUM(<?>,0,0,65535,ROWID)>=2 AND TBL$OR$IDX$PART$NUM(<?>,0,0,65535,ROWID)<=2) OR
(TBL$OR$IDX$PART$NUM(<?>,0,0,65535,ROWID)>=3 AND TBL$OR$IDX$PART$NUM(<?>,0,0,65535,ROWID)<=8)))
5 - access("C5"=1)
7 - filter("C5"=1)
관련 링크