해시 파티션과 ORA_HASH 함수

해시 파티션은 ORA_HASH 함수와 동일한 해시 알고리즘을 사용합니다. ORA_HASH 함수로 해시 파티션에 대한 skewness를 시뮬레이션할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1, t2 테이블은 64개의 해시 파티션으로 구성되어 있으며, t1 테이블은 1개, t2 테이블은 2개의 파티션 키로 해시 파티션을 생성합니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
 
CREATE TABLE t1 (c1) PARTITION BY HASH (c1) PARTITIONS 64 AS
SELECT ROWNUM FROM XMLTABLE ('1 to 64');
 
CREATE TABLE t2 (c1, c2) PARTITION BY HASH (c1, c2) PARTITIONS 64 AS
SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 64');

 

아래 쿼리의 결과에서 TBL$OR$IDX$PART$NUM 함수로 생성한 partition_position과 ORA_HASH 함수로 생성한 hash_value가 동일한 것을 확인할 수 있습니다.

-- 2
SELECT c1
     , TBL$OR$IDX$PART$NUM (TUNA.T1, 0, 1, 0, ROWID) AS partition_position
     , ORA_HASH (c1, 63) + 1 AS hash_value
  FROM t1 PARTITION FOR (1);
 
C1 PARTITION_POSITION HASH_VALUE
-- ------------------ ----------
 1                 56         56
15                 56         56
25                 56         56
38                 56         56
 
4 행이 선택되었습니다.

 

2개의 파티션 키로 생성한 해시 파티션은 2개의 해시 함수를 사용합니다.

-- 3
SELECT c1, c2
     , TBL$OR$IDX$PART$NUM (TUNA.T2, 0, 1, 0, ROWID) AS partition_position
     , ORA_HASH (c1, 31) + ORA_HASH (c2, 31) + 1 AS hash_value
  FROM t2 PARTITION FOR (1, 1);
 
C1 C2 PARTITION_POSITION HASH_VALUE
-- -- ------------------ ----------
 1  1                 47         47
15 15                 47         47
25 25                 47         47
29 29                 47         47
38 38                 47         47
 
5 행이 선택되었습니다.

 

아래 쿼리로 64개의 해시 파티션에 대한 NDV 별 skewness를 조회할 수 있습니다. 1024가 0.86으로 skewness가 가장 높고, 5120가 0.09로 kewness가 가장 낮습니다. 참고로 SKEWNESS_POP 함수는 Oracle 21c부터 사용할 수 있습니다.

-- 4
SET SERVEROUT ON
 
DECLARE
    v1 NUMBER;
BEGIN
    FOR i IN 1 .. 8 LOOP
        SELECT     SKEWNESS_POP (CEIL (COUNT (*) / i))
              INTO v1
              FROM DUAL
        CONNECT BY LEVEL <= i * 1024
          GROUP BY ORA_HASH (LEVEL, 63);
        
        DBMS_OUTPUT.PUT_LINE (i * 1024 || ':' || TO_CHAR (v1, '0.999999'));
    END LOOP;
END;
/
1024: 0.857664
2048: 0.255452
3072: 0.461798
4096: 0.568748
5120: 0.090917 -- !
6144: 0.238424
7168: 0.120469
8192: 0.167159
 
PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 쿼리로 파티션 별 NDV의 분포를 조회할 수 있습니다. SKEW가 없다면 NDV가 1024개인 경우 각각의 파티션에 16개(=1024/64)의 값이 저장되어야 합니다.

-- 5
SELECT     ORA_HASH (LEVEL, 63) + 1 AS partition_position
         , CEIL (COUNT (*) / :v1) AS cnt
      FROM DUAL
CONNECT BY LEVEL <= :v1 * 1024
  GROUP BY ORA_HASH (LEVEL, 63)
  ORDER BY partition_position;

 

아래 차트는 앞선 쿼리의 결과를 보여줍니다.