CNDBA社区
1. 说明
在18c中支持修改heap-organized表的分区策略，例如将hash分区改为范围分区，可以在线操作(不影响DML操作)。索引作为表修改的一部分进行维护。在修改分区策略时，所有索引列为新分区键前缀的未指定索引将自动转换为局部分区索引;否则，将索引转换为全局索引。但是不支持区域索引(domain indexes)，UPDATE INDEXES子句不能更改索引列表最初定义的列、索引的惟一性属性或任何其他索引属性。
2. 示例
下面将range分区修改为一个range-hash复合分区。
2.1. 创建range分区的分区表
CREATE TABLE mod_sales_partitioning
( prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (time_id)
(PARTITION sales_q1_2018 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy')),
PARTITION sales_q2_2018 VALUES LESS THAN (TO_DATE('01-JUL-2018','dd-MON-yyyy')),
PARTITION sales_q3_2018 VALUES LESS THAN (TO_DATE('01-OCT-2018','dd-MON-yyyy')),
PARTITION sales_q4_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy'))
);
2.1.1. 创建索引
CREATE INDEX i1_cust_id_indx ON mod_sales_partitioning (cust_id) LOCAL;
CREATE INDEX i2_time_id_indx ON mod_sales_partitioning (time_id);
CREATE INDEX i3_prod_id_indx ON mod_sales_partitioning (prod_id);
2.1.2. 查看分区类型
SELECT TABLE_NAME, PARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME PARTITION_TYPE
------------------------- --------------
MOD_SALES_PARTITIONING RANGE
2.1.3. 查看分区名称
SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME PARTITION_NAME
------------------------- --------------
MOD_SALES_PARTITIONING SALES_Q1_2018
MOD_SALES_PARTITIONING SALES_Q2_2018
MOD_SALES_PARTITIONING SALES_Q3_2018
MOD_SALES_PARTITIONING SALES_Q4_2018
...
2.2. 修改分区策略
ALTER TABLE mod_sales_partitioning
MODIFY
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 8
( PARTITION sales_q1_2018 VALUES LESS THAN (TO_DATE('01-APR-2018','dd-MON-yyyy')),
PARTITION sales_q2_2018 VALUES LESS THAN (TO_DATE('01-JUL-2018','dd-MON-yyyy')),
PARTITION sales_q3_2018 VALUES LESS THAN (TO_DATE('01-OCT-2018','dd-MON-yyyy')),
PARTITION sales_q4_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy')))
ONLINE
UPDATE INDEXES
( i1_cust_id_indx LOCAL,
i2_time_id_indx GLOBAL PARTITION BY RANGE (time_id)
(PARTITION ip1_indx VALUES LESS THAN (MAXVALUE) ) );
2.2.1. 查看分区和子分区的分区类型
SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME PARTITION SUBPARTIT
--------------------------- -------------- ----------
MOD_SALES_PARTITIONING RANGE HASH
2.2.2. 查看分区和子分区的分区名称
SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
--------------------------- ------------------ ------------------
MOD_SALES_PARTITIONING SALES_Q1_2018 SYS_SUBP567
MOD_SALES_PARTITIONING SALES_Q1_2018 SYS_SUBP568
MOD_SALES_PARTITIONING SALES_Q1_2018 SYS_SUBP569
MOD_SALES_PARTITIONING SALES_Q1_2018 SYS_SUBP570
...
版权声明：本文为博主原创文章，未经博主允许不得转载。
修改分区策略
