12.2 new feature of partition

Oracle database 12.2有不少分区加强的特性:

  • Multi-Column ListPartitioning
  • Auto list Partitioning
  • Interval SubPartitioning
  • Online Partition Maintenance Operation
  • Online Table Conversion to Partition Table
  • Filtered Partitioning Maintenance Operation
  • Read Only Partitions
  • 我们来列举几个看看:

    1. multi-column list partition。注:最多支持16个列

    Connected to Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 
    Connected as test@ORA122_windows_pdb122
     
    SQL>
    CREATE TABLE t_oracleblog (salername varchar(200),region VARCHAR2(50), channel VARCHAR2(50))
    PARTITION BY LIST (region, channel)  --Note keywork :region, channel, Here are 2 columns
    ( 
    partition p1 values ('USA','Direct'),
    partition p2 values ('USA','Partners'),
    partition p3 values ('GERMANY','Direct'),
    partition p4 values (('GERMANY','Partners'),('GERMANY','Web')),
    partition p5 values ('CHINA','Direct'),
    partition p6 values (('CHINA','Partners'),('CHINA','Web'),('CHINA','Oversee')),
    partition p7 values ('JAPAN','Direct'),
    partition p8 values (DEFAULT)
    )
    /
    
    insert into t_oracleblog values('AAA','USA','Direct');
    insert into t_oracleblog values('BBB','CHINA','Direct');
    insert into t_oracleblog values('CCC','CHINA','Web');
    insert into t_oracleblog values('DDD','CHINA','Partners');
    insert into t_oracleblog values('EEE','GERMANY','Direct');
    insert into t_oracleblog values('FFF','GERMANY','Partners');
    insert into t_oracleblog values('GGG','JAPAN','Direct');
    insert into t_oracleblog values('HHH','CHINA','Oversee');
    insert into t_oracleblog values('III','JAPAN','Web');
    insert into t_oracleblog values('JJJ','FRANCE','Direct');
    insert into t_oracleblog values('KKK','CHINA','DIRECT');
    
    SQL> select * from t_oracleblog partition(p1);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    AAA                  USA                                                Direct
     
    SQL> select * from t_oracleblog partition(p2);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
     
    SQL> select * from t_oracleblog partition(p3);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    EEE                  GERMANY                                            Direct
     
    SQL> select * from t_oracleblog partition(p4);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    FFF                  GERMANY                                            Partners
     
    SQL> select * from t_oracleblog partition(p5);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    BBB                  CHINA                                              Direct
     
    SQL> select * from t_oracleblog partition(p6);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    CCC                  CHINA                                              Web
    DDD                  CHINA                                              Partners
    HHH                  CHINA                                              Oversee
     
    SQL> select * from t_oracleblog partition(p7);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    GGG                  JAPAN                                              Direct
     
    SQL> select * from t_oracleblog partition(p8);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    III                  JAPAN                                              Web
    JJJ                  FRANCE                                             Direct
    KKK                  CHINA                                              DIRECT
     
    SQL> 
    

    2. auto-list partition

    CREATE TABLE t_car (brand VARCHAR2(50),model VARCHAR2(50), year char(4))
    PARTITION BY LIST (brand) AUTOMATIC --Note keywork :AUTOMATIC
    (
    partition p1 values ('BMW'),
    partition p2 values ('BENZ')
    )
    /
     
     
    SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
     
    TABLE_NAME                                                                       PARTITION_NAME
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    T_CAR                                                                            P1
    T_CAR                                                                            P2
     
    SQL> 
    SQL> 
    SQL> 
    SQL> 
    SQL> insert into t_car values('BMW','AAA','1984');
     
    1 row inserted
    SQL> insert into t_car values('BMW','BBB','1986');
     
    1 row inserted
    SQL> insert into t_car values('BENZ','CCC','1992');
     
    1 row inserted
    SQL> insert into t_car values('BENZ','DDD','1983');
     
    1 row inserted
     
    SQL> 
    SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
     
    TABLE_NAME                                                                       PARTITION_NAME
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    T_CAR                                                                            P1
    T_CAR                                                                            P2
     
    SQL> 
    SQL> 
    SQL> 
    SQL> insert into t_car values('JEEP','EEE','1991'); ---插入之前没有在partition key定义的行。
     
    1 row inserted
     
    SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR';
     
    TABLE_NAME                                                                       PARTITION_NAME
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    T_CAR                                                                            P1
    T_CAR                                                                            P2
    T_CAR                                                                            SYS_P1328
     
    SQL> 
    SQL> 
    SQL> 
    SQL> insert into t_car values('BYD','FFF','2015');
     
    1 row inserted
    SQL> insert into t_car values('FORD','FFF','2015');
     
    1 row inserted
     
    SQL> 
    SQL> 
    SQL> select table_name,partition_name from dba_tab_partitions where table_name='T_CAR'; --可以看到自动生成了新分区。
     
    TABLE_NAME                                                                       PARTITION_NAME
    -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
    T_CAR                                                                            P1
    T_CAR                                                                            P2
    T_CAR                                                                            SYS_P1328
    T_CAR                                                                            SYS_P1329
    T_CAR                                                                            SYS_P1330
     
    SQL> 
    

    3. interval subpartition

    4.online DDL for partition

    CREATE TABLE t_oracleblog (salername varchar(200),region VARCHAR2(50), channel VARCHAR2(50));
    
    ALTER TABLE t_oracleblog MODIFY 
    PARTITION BY LIST (region)
    (partition p1 values ('USA'),
    partition p2 values ('GERMANY'),
    partition p3 values ('JAPAN'),
    partition p4 values (DEFAULT)) 
    ONLINE ---Note keyword: ONLINE
    /
    

    注1:统计信息会收集

    注2:从10046的trace看,似乎是临时创建了SYS_JOURNAL_ ,SYS_RMTAB$$_H ,SYS_RMTAB$$_I以及上面的索引,在进行捣鼓,另外还有一堆数据字典的更新。没有看到类似dbms_redefinition在线重定义的功能的介入,没有看到在线重定义时关于物化视图create snaphot,和MLOG$_XXX这样的关键字。

    5. Filtered Partition on Maintenance Operations
    在MOVE,SPLIT,MERGE partition的时候,可以进行过滤:

    SQL> select * from T_ORACLEBLOG partition(p4);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    BBB                  CHINA                                              Direct
    CCC                  CHINA                                              Web
    DDD                  CHINA                                              Partners
    HHH                  CHINA                                              Oversee
    JJJ                  FRANCE                                             Direct
    KKK                  CHINA                                              DIRECT
     
    6 rows selected
     
    SQL> 
    SQL> 
    SQL> 
    SQL> 
    SQL> 
    SQL> 
    SQL> ALTER TABLE T_ORACLEBLOG MOVE PARTITION p4
      2  TABLESPACE SYSAUX
      3  INCLUDING ROWS WHERE REGION = 'CHINA' --Note keyword INCLUDING ROW WHERE
      4  /
     
    Table altered
     
    SQL> 
    SQL> select * from T_ORACLEBLOG partition(p4);
     
    SALERNAME            REGION                                             CHANNEL
    -------------------- -------------------------------------------------- --------------------------------------------------
    BBB                  CHINA                                              Direct
    CCC                  CHINA                                              Web
    DDD                  CHINA                                              Partners
    HHH                  CHINA                                              Oversee
    KKK                  CHINA                                              DIRECT
     
    SQL> 
    

    注1:where条件后面的字段千万不能写错,不然数据全没了。如错写成INCLUDING ROWS WHERE channel = ‘CHINA’,MOVE之后则分区4的数据全没了。因为including row表示留下的数据,而channel = ‘CHINA’ 这样的数据一条都没有,所以就清空了分区。

    6.Read only partition

    SQL> CREATE TABLE orders
      2  (
      3  order_id number,
      4  order_date DATE,
      5  customer_name varchar2(200)
      6  ) read only  ----Note keyword read only, which mean table read only
      7  PARTITION BY RANGE(order_date)
      8  (
      9  partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')),
     10  partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')),
     11  partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')),
     12  partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd')) read write ----Note keyword read only, which mean partition q4 read write
     13  )
     14  /
     
    Table created
     
    SQL> 
    SQL> 
    SQL> insert into orders values(1,to_date('2015-04-20','yyyy-mm-dd'),'AAA');
     
    1 row inserted
     
    SQL> insert into orders values(1,to_date('2015-06-20','yyyy-mm-dd'),'AAA');
     
    1 row inserted
     
    SQL> insert into orders values(1,to_date('2015-01-20','yyyy-mm-dd'),'AAA'); --对于read only的partition,插入数据会报错。
     
    insert into orders values(1,to_date('2015-01-20','yyyy-mm-dd'),'AAA')
     
    ORA-14466: Data in a read-only partition or subpartition cannot be modified.
     
    SQL> 
    SQL> 
    SQL> select * from orders;
     
      ORDER_ID ORDER_DATE  CUSTOMER_NAME
    ---------- ----------- --------------------------------------------------------------------------------
             1 2015/4/20   AAA
             1 2015/6/20   AAA
     
    SQL> 
    

    相关文章

    发表评论

    电子邮件地址不会被公开。 必填项已用*标注

    此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据