Oracle 11g 哈希、LIST分区测试

淡淡的烟草味﹌ 2024-04-18 00:08 68阅读 0赞

在哈希分区中，如果分区字段的值是相同的，相同值的条目都会存放在相同的分区中；  
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/，如需转载，请注明出处，否则将追究法律责任。

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