ORA_HASH()を使ってリストパーティションにハッシュパーティションのような均一配分を Tweet

ハッシュパーティションってリストパーティションみたいにパーティション狙い撃ちできて、かつ、ハッシュパーティションみたいに、データをパーティション間で均一化できないのかなぁ
というずいぶん昔の話を思い出して、そう言えば書いてないかもしれない。いつの話だよってぐらい昔の話だけどw
どうやったかというと、
ハッシュキーにふさわしい値をもつ列を決める（一意キーとか主キー列が理想、カーディナリティの低い、分布に偏りのあるデータを持つ列は使わない）

で、ハッシュキーが決まったら、話は早くて、ORA_HASH()関数で取得できるハッシュ値を利用したリストパーティションを作成するだけ。
ゴニョゴニュ言わなくても、SQLとPL/SQLのコードを見ていただければ、理解していただけるかと。

ORCL@SCOTT> l
  1  CREATE TABLE list_p_tab
  2  (
  3    id_code VARCHAR2(10) NOT NULL
  4    , foo VARCHAR2(30)
  5    , id_code_hash_value NUMBER(2) NOT NULL
  6  )
  7  PARTITION BY LIST (id_code_hash_value)
  8  (
  9    PARTITION list_p_tab_p1 VALUES(0)
 10    ,PARTITION list_p_tab_p2 VALUES(1)
 11    ,PARTITION list_p_tab_p3 VALUES(2)
 12    ,PARTITION list_p_tab_p4 VALUES(3)
 13* )
ORCL@SCOTT> /

Table created.

ORCL@SCOTT> ALTER TABLE list_p_tab ADD CONSTRAINT gpk_list_p_tab PRIMARY KEY(id_code) USING INDEX GLOBAL;

Table altered.

 ORCL@SCOTT> l
  1  DECLARE
  2    TYPE id_code_t IS TABLE OF list_p_tab.id_code%TYPE INDEX BY PLS_INTEGER;
  3    TYPE foo_t IS TABLE OF list_p_tab.foo%TYPE INDEX BY PLS_INTEGER;
  4    id_codes id_code_t;
  5    foos foo_t;
  6    k PLS_INTEGER := 1;
  7  BEGIN
  8    FOR i IN 1..400000 LOOP
  9        id_codes(k)            := TO_CHAR(i,'fm0000000009');
 10        foos(k)                := i;
 11        k := k + 1;
 12        IF k > 1000 THEN
 13          FORALL j in 1..k-1
 14            INSERT INTO list_p_tab VALUES(id_codes(j), foos(j), ORA_HASH(id_codes(j),3));
 15          COMMIT;
 16          k := 1;
 17        END IF;
 18    END LOOP;
 19* END;
ORCL@SCOTT> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.28

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'LIST_P_TAB',granularity=>'ALL',cascade=>true,no_invalidate=>false,degree=>4);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.51

ORCL@SCOTT> r
  1  select
  2    table_name
  3    ,partition_name
  4    ,num_rows
  5  from
  6    user_tab_partitions
  7  where
  8    table_name = 'LIST_P_TAB'
  9  order by
 10*   1,2

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
LIST_P_TAB                     LIST_P_TAB_P1                       99901
LIST_P_TAB                     LIST_P_TAB_P2                      100194
LIST_P_TAB                     LIST_P_TAB_P3                      100056
LIST_P_TAB                     LIST_P_TAB_P4                       99849

ORCL@SCOTT> select id_code_hash_value,count(1) from list_p_tab group by id_code_hash_value order by 1;

ID_CODE_HASH_VALUE   COUNT(1)
------------------ ----------
                 0      99901
                 1     100194
                 2     100056
                 3      99849

Elapsed: 00:00:00.06

ORCL@SCOTT> explain plan for 
  2  select
  3    *
  4  from
  5    list_p_tab
  6  where
  7    id_code_hash_value = 1;       

Explained.

Elapsed: 00:00:00.10
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2143708561

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   100K|  2054K|   275   (1)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|            |   100K|  2054K|   275   (1)| 00:00:01 |     2 |     2 |
|   2 |   TABLE ACCESS FULL   | LIST_P_TAB |   100K|  2054K|   275   (1)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------

グローバル索引を作成してあるので、パーティション関係ない検索は主キー索引経由でも可。

ORCL@SCOTT> explain plan for select * from list_p_tab where id_code = '00004000000';

Explained.

Elapsed: 00:00:00.02
ORCL@SCOTT> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
Plan hash value: 4132161764

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |     1 |    21 |     3   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| LIST_P_TAB     |     1 |    21 |     3   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | GPK_LIST_P_TAB |     1 |       |     2   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID_CODE"='00004000000')