Oracle 11g 哈希、LIST分区测试-蒲公英云

Oracle 11g 哈希、LIST分区测试

在哈希分区中，如果分区字段的值是相同的，相同值的条目都会存放在相同的分区中；
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博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2125321/，如需转载，请注明出处，否则将追究法律责任。

转载于//blog.itpub.net/26506993/viewspace-2125321/