RANGE分区表操作指南-优快云博客

------------创建RANGE分区表。
create table t_partition_range (BAZ001 NUMBER(16),
BAZ002 NUMBER(16),
AAB001 NUMBER(16) not null,
AAC001 NUMBER(16) not null,
AAE140 VARCHAR2(3) not null,
AAC030 DATE not null,
AAC008 VARCHAR2(3) not null,
AAC049 DATE,
AAE200 NUMBER(3),
AAB034 VARCHAR2(16) not null,
AAA027 VARCHAR2(6),
BAZ003 NUMBER(16),
BAZ004 VARCHAR2(1)
)
partition by range(aac030)
(partition p1 values less than(TO_DATE('2000-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
-------------插入数据
insert /*+ append */into t_partition_range select * from ac02 WHERE ROWNUM<10000;
COMMIT;
------------查询数据
SELECT * FROM t_partition_range PARTITION(p5);
-------------创建索引
CREATE INDEX ind_partition_range_1 ON t_partition_range(aac001);--普通索引
-----------查询分区信息
SELECT * FROM User_Part_Tables;
SELECT * FROM User_Tab_Partitions;
SELECT * FROM User_Part_Indexes;
SELECT * FROM User_Ind_Partitions;
SELECT * FROM User_Indexes;
-------------删除分区
ALTER TABLE t_partition_range DROP PARTITION p4;
------------重新编译索引
ALTER INDEX idx_parti_range_id REBUILD ;
------------带子句update indexes删除分区
ALTER TABLE t_partition_range DROP PARTITION p4 UPDATE INDEXES;
------------分裂分区，每次只能分裂两分区
alter table t_partition_range split partition p5 at (TO_DATE('2003-1-1', 'YYYY-MM-DD')) into
(partition p6 ,PARTITION p5 ) UPDATE INDEXES;
------------合并分区，合并的两分区顺序必须是小的值在前面。
alter table t_partition_range merge PARTITIONS p6,p5 into partition p7;
------------增加分区,所要增加的分区值必须要大于当前分区中的最大值
alter table t_partition_range add partition p4 values less than(TO_DATE('2006-1-1', 'YYYY-MM-DD')) UPDATE INDEXES ;
------------创建本地索引
CREATE INDEX ind_partition_range_2 ON t_partition_range(aac001) LOCAL;
------------创建全局分区索引
create index idx_parti_range_id on t_partition_range(aac001)
GLOBAL partition by HASH(AAC001)(
PARTITION i_range_p1 ,
PARTITION i_range_p2 ,
PARTITION i_range_P3 );
------------删除全局分区索引
ALTER INDEX idx_parti_range_id DROP PARTITION i_range_p2;
-----------重新编译索引
ALTER INDEX idx_parti_range_id REBUILD PARTITION i_range_p3 ;