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

迷南。 2022-06-17 13:49 225阅读 0赞

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