在雜湊分割槽中，如果分割槽欄位的值是相同的，相同值的條目都會存放在相同的分割槽中；
LIST分割槽則易於管理，而對於雜湊分割槽，則不好控制各個值所存放的分割槽，有的分割槽會存放兩個或多個分割槽欄位值。

建立測試表
CREATE TABLE Campaign(compaign_id number, customer_name varchar2(20));
ALTER TABLE Campaign ADD primary key(compaign_id);

建立雜湊分割槽表，外來鍵引用上面建立的表
CREATE TABLE Target(request_id number,compaign_id number,
CONSTRAINT compaign_id_fk
    FOREIGN KEY (compaign_id) REFERENCES Campaign(compaign_id))
PARTITION BY HASH (compaign_id)
PARTITIONS 64;
ALTER TABLE Target ADD primary key(request_id);

向第一張表中批次插入資料
BEGIN
FOR i IN 1..100 LOOP
insert into Campaign select i, dbms_random.string('U', 10) from dual;
END LOOP;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

向雜湊分割槽表中批次插入資料
BEGIN
FOR k in (select compaign_id from Campaign order by 1) LOOP
FOR i IN 1..1000 LOOP
insert into Target select dbms_random.value(1,1000000),k.compaign_id from dual;
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

檢視執行計劃
explain plan for select * from target where COMPAIGN_ID=70;
select * from table(dbms_xplan.display);
Plan hash value: 940899126

------------------------------------------------------------------------------------------------
| Id | Operation             | Name   | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        | 4000 |    97K|    26   (0)| 00:00:01 |       |       |
|   1 | PARTITION HASH SINGLE|        | 4000 |    97K|    26   (0)| 00:00:01 |    42 |    42 |
|* 2 |   TABLE ACCESS FULL   | TARGET | 4000 |    97K|    26   (0)| 00:00:01 |    42 |    42 |
------------------------------------------------------------------------------------------------

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

   2 - filter("COMPAIGN_ID"=70)

建立LIST分割槽表，外來鍵引用上面建立的表
CREATE TABLE Target2(request_id number,compaign_id number,
CONSTRAINT compaign_id_fk2
    FOREIGN KEY (compaign_id) REFERENCES Campaign(compaign_id))
PARTITION BY LIST(compaign_id)
( PARTITION p_default VALUES (DEFAULT));
ALTER TABLE Target2 ADD primary key(request_id);

向LIST分割槽表中批次插入資料
BEGIN
FOR k in (select compaign_id from Campaign order by 1) LOOP
FOR i IN 1..1000 LOOP
insert into Target2 select dbms_random.value(1,1000000),k.compaign_id from dual;
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

將LIST分割槽拆分，將1、50拆分成兩個單獨的分割槽
ALTER TABLE Target2 SPLIT PARTITION p_default
      VALUES (1)
      INTO (PARTITION p_1, PARTITION p_default);
ALTER TABLE Target2 SPLIT PARTITION p_default
      VALUES (50)
      INTO (PARTITION p_50, PARTITION p_default);
select count(*) from target2 partition(p_default);

檢視執行計劃
explain plan for select * from target2 where COMPAIGN_ID=50;
select * from table(dbms_xplan.display);
Plan hash value: 1725981576

-------------------------------------------------------------------------------------------------
| Id | Operation             | Name    | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         | 5000 |   122K|    10   (0)| 00:00:01 |       |       |
|   1 | PARTITION LIST SINGLE|         | 5000 |   122K|    10   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS FULL   | TARGET2 | 5000 |   122K|    10   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------

Oracle 11g 雜湊、LIST分割槽測試

相關文章