droptable t1 purge; createtable t1 (a number,b number,c number) partition by range(a) (partition p1 values less than (1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000));
insertinto t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connectbylevel >= 2); commit;
createindex idx_local on t1(b) local; createindex idx_global on t1(c);
删除分区
1 2 3 4
ALTERTABLE t1 DROP PARTITIONS p1,p2;
Table altered.
检查分区和索引
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
SELECTTABLE_NAME, partition_name FROM user_tab_partitions WHERETABLE_NAME='T1';
droptable t1 purge; createtable t1 (a number,b number,c number) partition by range(a) (partition p1 values less than (1000), partition p2 values less than(2000), partition p3 values less than(3000), partition p4 values less than(4000));
insertinto t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connectbylevel >= 2); commit;
createindex idx_local on t1(b) local; createindex idx_global on t1(c);
ALTER INDEX REBUILD [PARTITION]: 重建整个索引或索引分区,重建完之后的索引不会包含任何陈旧的条目
1
ALTERINDEX INDEXNAME REBUILD;
ALTER INDEX COALESCE [PARTITION] CLEANUP: 清理所有索引块中孤立的条目
1
ALTERINDEX INDEXNAME COALESCE CLEANUP;
合并多个分区
12c开始允许将多个分区或子分区合并到一个分区或子分区。
range分区/子分区
当合并多个range分区时,分区必须是相邻的,并按分区的边界值的升序指定。
新分区继承了原分区的最高边界
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE TABLE prod_list (Prod_name VARCHAR2(30), Prod_id NUMBER(4)) PARTITIONBY RANGE(prod_id) ( PARTITION prod_1 VALUES LESS THAN (100), PARTITION prod_2 VALUES LESS THAN (200), PARTITION prod_3 VALUES LESS THAN (300), PARTITION prod_4 VALUES LESS THAN (400), PARTITION prod_5 VALUES LESS THAN (500), PARTITION prod_6 VALUES LESS THAN (600), PARTITION prod_other VALUES LESS THAN (MAXVALUE) );
ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTOpartition old;
droptable prod_list CREATETABLE prod_list (Prod_name VARCHAR2(30), Prod_id NUMBER(4)) PARTITION BY RANGE(prod_id) ( PARTITION prod100 VALUES LESS THAN (100), PARTITION prod200 VALUES LESS THAN (200), PARTITION prod300 VALUES LESS THAN (300), PARTITION prodother VALUES LESS THAN (MAXVALUE) );
alter table prod_list split partition prod100 into (partition prod25 values less than (25), partition prod50 values less than (50), partition prod75 values less than (75), partition prod100);
CREATE TABLE range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITIONBYRANGE (time_id) (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')), PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')), PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')), PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));
ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')), PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')), PARTITION sales_mar_2012);