Oracle 分区

自增分区

numtoyminterval 按年、月分区

numtoyminterval(1, ‘year’)、numtoyminterval(1, ‘month’)

numtodsinterval 按天、时、分、秒分区

numtodsinterval(1, ‘day’)、numtodsinterval(1, ‘hour’)、numtodsinterval(1, ‘minute’)、numtodsinterval(1, ‘second’)

例子

create table user (id number, create_dt date)
	tablespace users
	partition by range (create_dt)
	interval(numtoyminterval(1, 'month'))
	(
        partition p1 values less than (to_date('2018-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
    );

清空、删除分区操作

# 创建分区表
略

# 检查分区表及数据
sql> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;

# 创建主键和索引
sql> alter table TEST_PARTAS add constraint pk_id primary key(ID);
sql> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);

# 检查索引状态，当前状态可用
sql> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID

# 用truncate 删除p0分区数据，不加update index参数
sql> alter table test_partas truncate partition p0;

# 检查索引状态，状态不可用
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE

# 重建立索引，要加online ，尽量减小对业务的冲击
sql> alter index PK_ID rebuild online;
sql> alter index IND_ACCOUNT_ID rebuild online;
此时索引恢复正常可用状态

# 用truncate 删除p1分区数据，增加update index参数
sql> alter table test_partas truncate partition p1 update indexes;
此时索引是正常可用状态

扩展

如果 truncate 清空分区数据、drop 删除分区，索引都会失效

sql> alter table test_partas drop partition SYS_P1611;

CREATE TABLE IM_WX_PAGEOPEN_LOG_02 NOLOGGING AS
    SELECT * FROM IM_WX_PAGEOPEN_LOG;

ALTER TABLE IM_WX_PAGEOPEN_LOG DROP PARTITION P2020Y010;
ALTER TABLE IM_WX_PAGEOPEN_LOG DROP PARTITION P2020Y11;
ALTER TABLE IM_WX_PAGEOPEN_LOG DROP PARTITION P2020Y12;

ALTER TABLE IM_WX_PAGEOPEN_LOG ADD PARTITION P2020Y09 VALUES LESS THAN(TO_DATE('2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));
ALTER TABLE IM_WX_PAGEOPEN_LOG ADD PARTITION P2020Y10 VALUES LESS THAN(TO_DATE('2020-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));
ALTER TABLE IM_WX_PAGEOPEN_LOG ADD PARTITION P2020Y11 VALUES LESS THAN(TO_DATE('2020-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));
ALTER TABLE IM_WX_PAGEOPEN_LOG ADD PARTITION P2020Y12 VALUES LESS THAN(TO_DATE('2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'));

查询索引状态不可用

SELECT T.INDEX_NAME, T.TABLE_NAME, T.STATUS
  FROM USER_INDEXES T
 WHERE T.TABLE_NAME IN (SELECT T.TABLE_NAME FROM USER_TABLES T);