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'))

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!