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,to_date('2015-01-01','yyyy-mm-dd')), 
  partition p3 values less than ('0302', 1,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p4 values less than ('0302', 2,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p5 values less than ('0303', 1,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p6 values less than ('0303', 2,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p_other values less than(default,maxvalue,maxvalue)   
);

--改成如下的形式
drop table test purge;
create table test
(
  id number,
  created date,
  code VARCHAR2(10),
  power_flag number(1)
)
partition by range(power_flag,created)
(
  partition p1 values less than ( 1,to_date('2015-01-01','yyyy-mm-dd')),
  partition p2 values less than ( 1,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p3 values less than ( 1,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p4 values less than ( 2,to_date('2015-01-01','yyyy-mm-dd')), 
  partition p5 values less than ( 2,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p6 values less than ( 2,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p_other values less than(maxvalue,maxvalue)   
);
insert into test(id,power_flag,created) values(11,1,to_date('2014-01-01','yyyy-mm-dd'));
insert into test(id,power_flag,created) values(22,1,to_date('2015-11-01','yyyy-mm-dd'));
insert into test(id,power_flag,created) values(33,2,to_date('2014-01-01','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 date,
  code VARCHAR2(10),
  power_flag number(1)
)
partition by range(power_flag,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,to_date('2015-01-01','yyyy-mm-dd')),
  partition p2 values less than ( 1,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p3 values less than ( 1,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p4 values less than ( 2,to_date('2015-01-01','yyyy-mm-dd')), 
  partition p5 values less than ( 2,to_date('2016-01-01','yyyy-mm-dd')), 
  partition p6 values less than ( 2,to_date('2017-01-01','yyyy-mm-dd')), 
  partition p_other values less than(maxvalue,maxvalue)  
);
insert into test(id,code,power_flag,created) values(11,'0301',1,to_date('2014-01-01','yyyy-mm-dd'));
insert into test(id,code,power_flag,created) values(22,'0301',1,to_date('2015-11-01','yyyy-mm-dd'));
insert into test(id,code,power_flag,created) values(33,'0302',2,to_date('2014-01-01','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'))