Oracle 11g如何实现"三重分区"

有的时候有的场景需要实现三重分区，但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'))