有的时候有的场景需要实现三重分区，但Oracle只是提供了两重，怎么办？可以实现一个类似于三重分区的功能：
--下面的建表语句会报错，code为varchar，range只支持number和date create table test ( id number,created date,code VARCHAR2(10),power_flag number(1) ) partition by range(code,power_flag,created) ( partition p1 values less than ('0301',1,to_date('2015-01-01','yyyy-mm-dd')),partition p2 values less than ('0301',2,partition p3 values less than ('0302',to_date('2016-01-01',partition p4 values less than ('0302',partition p5 values less than ('0303',to_date('2017-01-01',partition p6 values less than ('0303',partition p_other values less than(default,maxvalue,maxvalue) ); --改成如下的形式 drop table test purge; create table test ( id number,power_flag number(1) ) partition by range(power_flag,created) ( partition p1 values less than ( 1,partition p2 values less than ( 1,partition p3 values less than ( 1,partition p4 values less than ( 2,partition p5 values less than ( 2,partition p6 values less than ( 2,partition p_other values less than(maxvalue,maxvalue) ); insert into test(id,created) values(11,to_date('2014-01-01','yyyy-mm-dd')); insert into test(id,created) values(22,to_date('2015-11-01',created) values(33,'yyyy-mm-dd')); commit; sql> select * from test where power_flag=1; 执行计划 ---------------------------------------------------------- Plan hash value: 3957568297 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 84 | 6 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ITERATOR| | 2 | 84 | 6 (0)| 00:00:01 | 1 | 4 | |* 2 | TABLE ACCESS FULL | TEST | 2 | 84 | 6 (0)| 00:00:01 | 1 | 4 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("POWER_FLAG"=1) sql> select * from test where power_flag=1 and created=to_date('2014-01-01','yyyy-mm-dd'); 执行计划 ---------------------------------------------------------- Plan hash value: 2295545728 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 42 | 3 (0)| 00:00:01 | 1 | 1 | |* 2 | TABLE ACCESS FULL | TEST | 1 | 42 | 3 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("POWER_FLAG"=1 AND "CREATED"=TO_DATE(' 2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')) drop table test purge; create table test ( id number,created) subpartition by list(code) subpartition template ( subpartition sub1 values('0301'),subpartition sub2 values('0302'),subpartition sub3 values('0303'),subpartition sub4 values('0304'),subpartition sub_default values(default) ) ( partition p1 values less than ( 1,maxvalue) ); insert into test(id,code,'0301','0302','yyyy-mm-dd')); commit; sql> select * from test where power_flag=1; 执行计划 ---------------------------------------------------------- Plan hash value: 947334805 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 84 | 6 (0)| 00:00:01 | | | | 1 | PARTITION RANGE ITERATOR| | 2 | 84 | 6 (0)| 00:00:01 | 1 | 4 | | 2 | PARTITION LIST ALL | | 2 | 84 | 6 (0)| 00:00:01 | 1 | 5 | |* 3 | TABLE ACCESS FULL | TEST | 2 | 84 | 6 (0)| 00:00:01 | 1 | 20 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("POWER_FLAG"=1) sql> select * from test where power_flag = 1 and created = to_date('2014-01-01','yyyy-mm-dd'); 执行计划 ---------------------------------------------------------- Plan hash value: 2653427471 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 42 | 3 (0)| 00:00:01 | 1 | 1 | | 2 | PARTITION LIST ALL | | 1 | 42 | 3 (0)| 00:00:01 | 1 | 5 | |* 3 | TABLE ACCESS FULL | TEST | 1 | 42 | 3 (0)| 00:00:01 | 1 | 5 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("POWER_FLAG"=1 AND "CREATED"=TO_DATE(' 2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss')) sql> select * from test where power_flag = 1 and code ='0301' and created = to_date('2014-01-01','yyyy-mm-dd'); 执行计划 ---------------------------------------------------------- Plan hash value: 732709485 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 3 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 1 | 42 | 3 (0)| 00:00:01 | 1 | 1 | | 2 | PARTITION LIST SINGLE| | 1 | 42 | 3 (0)| 00:00:01 | KEY | KEY | |* 3 | TABLE ACCESS FULL | TEST | 1 | 42 | 3 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("POWER_FLAG"=1 AND "CREATED"=TO_DATE(' 2014-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss'))
