表分区扩展及清理-记录01

一、项目背景

项目涉及表分区扩展和清理，分区键是表的LAST_DATE字段，只保留其最近的3天数据。每天程序自动跑一次，当存储过程被调用时，先用split扩展出下一天的分区，然后再去drop掉3天前的分区数据，最后涉及索引的rebuild。

二、表分区分类

1、 按Range进行分区

范围分区将数据基于范围映射到每一个分区，分区键经常采用日期。比如一个表上有一个日期字段LAST_DATE，我们想将2020年0201之前的数据放在分区1，将0201-0301的数据放在分区2，将0301-0401…以此类推，那么我们可以选择在此表的这个日期字段LAST_DATE加上Range分区。

CREATE TABLE TAB_TEST
(
CINO VARCHAR2(20) NOT NULL,
LIST_TYPE VARCHAR2(10) NOT NULL,
FLAG CHAR(1),
LAST_DATE VARCHAR2(8)
)
PARTITION BY RANGE(LAST_DATE)
(
//这里的20200201是小于，不包含的意思
PARTITION PART_01 VALUES LESS THAN (TO_DATE('20100201','YYYY-MM-DD')) TABLESPACE TABLESPACE_01,
PARTITION PART_02 VALUES LESS THAN (TO_DATE('20100301','YYYY-MM-DD')) TABLESPACE TABLESPACE_02,
PARTITION PART_03 VALUES LESS THAN (TO_DATE('20100401','YYYY-MM-DD')) TABLESPACE TABLESPACE_03,
//这里的MAXVALUE代表不确定的值，可以将此分区进行split，拆成2个分区。
PARTITION P_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TABLESPACE_PMAX
)

范围分区使用规则：
1）每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2）所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3）在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
4）如果某些记录无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

2、按List进行分区

该项目采用List分区
LIST分区的特点是某列的值只有几个，基于这样的特点可以采用列表分区。创建一个按字段数据列表固定可枚举值分区的表。一旦插入的列值不在该分区范围内，则插入失败，所以我们在建list分区时，会创建default分区存储那些不在指定范围内的数据，类似range分区的maxvalue分区。

CREATE TABLE TAB_TEST
(
CINO VARCHAR2(20) NOT NULL,
LIST_TYPE VARCHAR2(10) NOT NULL,
FLAG CHAR(1),
LAST_DATE VARCHAR2(8)
)
PARTITION BY LIST(LAST_DATE)
(
PARTITION PART_01 VALUES ('20100201') TABLESPACE TABLESPACE_01,
PARTITION PART_02 VALUES ('20100202') TABLESPACE TABLESPACE_02,
PARTITION PART_01 VALUES ('20100203') TABLESPACE TABLESPACE_03,
//这里的DEFAULT代表其他值，也可以将此分区进行split，拆成2个分区。
PARTITION P_OTHER VALUES (DEFAULT) TABLESPACE TABLESPACE_OTHER
)

3、常用的分区语句

===============1、添加分区==============
//RANGE分区含有MAXVALUE、LIST分区含有DEFAULT，增加分区均不能使用add partition ,而是需要将max/default分区split
//SPLIT会导致global索引不可用，而local索引的相应分区也为unusable

//RANGE分区spilt max 分区
ALTER TABLE TAB_TEST SPLIT PARTITION P_MAX 
AT (TO_DATE('20100501','YYYY-MM-DD')) INTO
(
PARTITION PART_04 TABLESPACE TABLESPACE_04,
PARTITION P_MAX TABLESPACE TABLESPACE_PMAX
) update index

//LIST分区split default分区
ALTER TABLE TAB_TEST SPLIT PARTITION P_OTHER 
VALUES ('20100204') INTO
(
PARTITION PART_04 TABLESPACE TABLESPACE_04,
PARTITION P_OTHER TABLESPACE TABLESPACE_POTHER
) update index

//针对不含MAXVALUE的RANGE分区，add partition 
ALTER TABLE TAB_TEST ADD PARTITION P_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TABLESPACE_PMAX;

//针对不含DEFAULT的LIST分区，add partition 
ALTER TABLE TAB_TEST ADD PARTITION P_OTHER VALUES (DEFAULT) TABLESPACE TABLESPACE_POTHER;
 
===============2、分区的修改==============
 //针对LIST的某个分区，增加一个值，比如，之前是将列值为‘5’的数据放在分区1，现在想增加将列值为‘50’的数据也放在分区1
 ALTER TABLE TAB_TEST MODIFY PARTITION PART_01 ADD VALUES('50');
 //将列值为‘50’的去掉
 ALTER TABLE TAB_TEST MODIFY PARTITION PART_01 DROP VALUES('50');
==============3、分区清理==============
//drop分区，将分区数据及分区全部删除
ALTER TABLE TAB_TEST DROP PARTITION PART_01 update global indexes;
//truncate分区，只是删除分区中存放的数据
ALTER TABLE TAB_TEST TRUNCATE PARTITION PART_01 update global indexes;

==============4、分区查询==============
//某个分区存放的数据查询
SELECT * FROM TAB_TEST PARTITION (PART_01);
//查看某个表的索引信息
SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='TAB_TEST';
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TAB_TEST';
//查看某个表的索引
SELECT * FROM DBA_INDEXES
//查看某个表的分区索引
SELECT * FROM DBA_PART_INDEXES 

为测试分享，如有错误，请多多指正，谢谢～～