在哈希分区中，如果分区字段的值是相同的，相同值的条目都会存放在相同的分区中；
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 |
-------------------------------------------------------------------------------------------------

ITPub博客

Oracle 11g 哈希、LIST分区测试

最新文章