[分区表] 数据仓库滑动窗口技术

# 滑动窗口技术
# 对分区表做任何操作时, 同时维护全局索引

1. 分区表
CREATE TABLE t (timestamp date,id int)
PARTITION BY RANGE (timestamp)
(
    PARTITION fy_2004 VALUES LESS THAN (to_date('01-jan-2005','dd-mon-yyyy')),
    PARTITION fy_2005 VALUES LESS THAN(to_date('01-jan-2006','dd-mon-yyyy'))
);

2. 初始化数据
insert into t partition(fy_2004) select to_date('2004-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects；
insert into t partition(fy_2005) select to_date('2005-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;

3. 创建局部索引
create index partitioned_idx_local on t (id) local;

4. 创建全局索引
create index partitioned_idx_global on t (timestamp) GLOBAL;

5. 创建旧数据交换空表
create table fy_2004 (timestamp date,id int);
create index fy_2004_idx on fy_2004(id);

6. 创建新数据交换空表
create table fy_2006 (timestamp date,id int)；

7. 插入新数据
insert into fy_2006 select to_date('2006-12-31','yyyy-mm-dd')-mod(rownum,360),object_id from all_objects;
create index fy_2006_idx on fy_2006(id) nologging；

8. 交换出旧数据分区
alter table t exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES;

9. 删除旧数据分区, 同时维护全局索引
alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES;

10. 添加新数据分区
alter table partitioned add partition fy_2006 values less than (to_date('2007-01-01','yyyy-mm-dd'));

11. 滑入新数据, 同时维护全局索引
alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation UPDATE GLOBAL INDEXES;