MOD 함수나 ORA_HASH 함수를 사용한 가상 칼럼을 파티션 키로 사용하면 해시 파티션과 유사한 리스트 파티션을 생성할 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. ORA_HASH (c1, 3) 표현식으로 c2 가상 칼럼을 생성하고, c2 칼럼을 파티션 키로 가진 4개의 리스트 파티션을 생성합니다. 데이터 타입이 문자 타입이라면 MOD 함수 대신 ORA_HASH 함수를 사용할 수 있습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
c1 NUMBER
, c2 NUMBER AS (MOD (c1, 4))
--, c2 NUMBER AS (ORA_HASH (c1, 3))
)
PARTITION BY LIST (c2) (
PARTITION p0 VALUES (0)
, PARTITION p1 VALUES (1)
, PARTITION p2 VALUES (2)
, PARTITION p3 VALUES (3)
);
t1 테이블에 1,000행을 입력하고 통계 정보를 수집하겠습니다.
-- 2-1
INSERT INTO t1 (c1) SELECT ROWNUM FROM XMLTABLE ('1 to 1000');
COMMIT;
-- 2-2
EXEC DBMS_STATS.GATHER_TABLE_STATS (NULL, 'T1');
*_TAB_PARTITIONS 뷰를 조회하면 파티션 별로 균등한 데이터가 입력된 것을 확인할 수 있습니다.
SELECT partition_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'T1';
PARTITION_NAME NUM_ROWS
-------------- --------
P0 250
P1 250
P2 250
P3 250
4 행이 선택되었습니다.
아래 예제는 파티션 Pruning이 정상적으로 동작하는 것을 보여줍니다. 파티션 해시 파티션과 달리 WHERE 절의 조건으로 특정 파티션을 조회할 수 있습니다.
-- 4-1
SELECT * FROM t1;
---------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1000 |
| 1 | PARTITION LIST ALL| | 1 | 1 | 4 | 1000 |
| 2 | TABLE ACCESS FULL| T1 | 4 | 1 | 4 | 1000 |
---------------------------------------------------------------------
-- 4-2
SELECT * FROM t1 PARTITION (p0);
------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 250 |
| 1 | PARTITION LIST SINGLE| | 1 | 1 | 1 | 250 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 | 250 |
------------------------------------------------------------------------
-- 4-3
SELECT * FROM t1 WHERE c2 = 0;
------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 250 |
| 1 | PARTITION LIST SINGLE| | 1 | 1 | 1 | 250 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 1 | 1 | 250 |
------------------------------------------------------------------------
-- 4-4
SELECT * FROM t1 WHERE c2 IN (0, 1);
------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 500 |
| 1 | PARTITION LIST INLIST| | 1 |KEY(I) |KEY(I) | 500 |
| 2 | TABLE ACCESS FULL | T1 | 2 |KEY(I) |KEY(I) | 500 |
------------------------------------------------------------------------
-- 4-5
SELECT * FROM t1 WHERE c2 BETWEEN 0 AND 2;
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 750 |
| 1 | PARTITION LIST ITERATOR| | 1 | 1 | 3 | 750 |
| 2 | TABLE ACCESS FULL | T1 | 3 | 1 | 3 | 750 |
--------------------------------------------------------------------------
아래 예제는 조인 동작을 보여줍니다. 5-1번 쿼리는 일반 조인으로 동작하고, 파티션 키를 조인 조건(b.c2 = a.c2)으로 사용한 5-2번 쿼리는 Full Partition-Wise 조인으로 동작합니다.
-- 5-1
SELECT * FROM t1 a, t1 b WHERE b.c1 = a.c1;
----------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1000 |
|* 1 | HASH JOIN | | 1 | | | 1000 | -- !
| 2 | PARTITION LIST ALL| | 1 | 1 | 4 | 1000 |
| 3 | TABLE ACCESS FULL| T1 | 4 | 1 | 4 | 1000 |
| 4 | PARTITION LIST ALL| | 1 | 1 | 4 | 1000 |
| 5 | TABLE ACCESS FULL| T1 | 4 | 1 | 4 | 1000 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."C1"="A"."C1")
-- 5-2
SELECT * FROM t1 a, t1 b WHERE b.c1 = a.c1 AND b.c2 = a.c2;
----------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | A-Rows |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1000 |
| 1 | PARTITION LIST ALL | | 1 | 1 | 4 | 1000 |
|* 2 | HASH JOIN | | 4 | | | 1000 | -- !
| 3 | TABLE ACCESS FULL| T1 | 4 | 1 | 4 | 1000 |
| 4 | TABLE ACCESS FULL| T1 | 4 | 1 | 4 | 1000 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."C2"="A"."C2" AND "B"."C1"="A"."C1")
아래 예제는 병렬 조인의 동작을 보여줍니다. 6-1번 쿼리는 파티션 그래뉼로 동작하고, 6-2번 쿼리는 해시 방식으로 분배되어 PART JOIN FILTER가 사용되었습니다.
-- 6-1
SELECT /*+ PARALLEL(2) */
*
FROM t1 a, t1 b
WHERE b.c1 = a.c1
AND b.c2 = a.c2;
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | A-Rows |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | | 1000 |
| 1 | PX COORDINATOR | | 1 | | | | | | 1000 |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 0 | | | Q1,00 | P->S | QC (RAND) | 0 |
| 3 | PX PARTITION LIST ALL| | 2 | 1 | 4 | Q1,00 | PCWC | | 1000 |
|* 4 | HASH JOIN | | 4 | | | Q1,00 | PCWP | | 1000 | -- !
| 5 | TABLE ACCESS FULL | T1 | 4 | 1 | 4 | Q1,00 | PCWP | | 1000 |
| 6 | TABLE ACCESS FULL | T1 | 4 | 1 | 4 | Q1,00 | PCWP | | 1000 |
-----------------------------------------------------------------------------------------------------------
Outline Data
-------------
PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" NONE NONE)
-- 6-2
SELECT /*+ PARALLEL(2) PQ_DISTRIBUTE (B HASH HASH) */
*
FROM t1 a, t1 b
WHERE a.c2 = 1
AND b.c1 = a.c1
AND b.c2 = a.c2;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | A-Rows |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | | | 250 |
| 1 | PX COORDINATOR | | 1 | | | | | | 250 |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | | | Q1,02 | P->S | QC (RAND) | 0 |
|* 3 | HASH JOIN BUFFERED | | 2 | | | Q1,02 | PCWP | | 250 |
| 4 | PART JOIN FILTER CREATE| :BF0000 | 2 | | | Q1,02 | PCWP | | 250 | -- !
| 5 | PX RECEIVE | | 2 | | | Q1,02 | PCWP | | 250 |
| 6 | PX SEND HASH | :TQ10000 | 0 | | | Q1,00 | P->P | HASH | 0 |
| 7 | PX BLOCK ITERATOR | | 2 | 1 | 1 | Q1,00 | PCWC | | 250 |
|* 8 | TABLE ACCESS FULL | T1 | 5 | 1 | 1 | Q1,00 | PCWP | | 250 |
| 9 | PX RECEIVE | | 2 | | | Q1,02 | PCWP | | 250 |
| 10 | PX SEND HASH | :TQ10001 | 0 | | | Q1,01 | P->P | HASH | 0 |
| 11 | PX BLOCK ITERATOR | | 1 |KEY(AP)|KEY(AP)| Q1,01 | PCWC | | 250 |
|* 12 | TABLE ACCESS FULL | T1 | 5 | 1 | 1 | Q1,01 | PCWP | | 250 |
--------------------------------------------------------------------------------------------------------------
Outline Data
-------------
PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" HASH HASH)
파티션 키로 사용한 가상 칼럼을 변경하면 에러가 발생합니다. 이로 인해 해시 파티션보다 유연성이 떨어지는 단점이 있습니다.
-- 7
ALTER TABLE t1 MODIFY c2 NUMBER AS (MOD (c1, 6));
ORA-54019: 분할 열이므로 가상 열 표현식을 변경할 수 없습니다.