ORACLE Partition Table

Create Partition Table：

CREATE TABLE sales_history
(
   sal_date   DATE NOT NULL,
   sal_id     NUMBER NOT NULL,
   sal_row    NUMBER (12) NOT NULL
)
PARTITION BY RANGE (sal_date)
   (PARTITION sal_11
       VALUES LESS THAN (TO_DATE ('2012-05-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_12
       VALUES LESS THAN (TO_DATE ('2012-08-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_13
       VALUES LESS THAN (TO_DATE ('2012-11-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_14
       VALUES LESS THAN (TO_DATE ('2013-02-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_15
       VALUES LESS THAN (TO_DATE ('2013-05-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_16
       VALUES LESS THAN (TO_DATE ('2013-08-01', 'YYYY-MM-DD')) tablespace users,
    PARTITION sal_other
       VALUES LESS THAN (maxvalue) tablespace users)
NOLOGGING;

 

Delete Partition Table：

drop  table sales_history;

 

Add New Partition：

alter table <table name> add partition <partition name> values less than (to_date('2013.09.01','yyyy-mm-dd')) tablespace <tablespace name>;

 

Delete Partition：alter table <table name> drop partition <partition name>;

 

If table has global indexes，script should be：

ALTER TABLE <table name> DROP PARTITION <partition name> UPDATE GLOBAL INDEXES;

 

Clear the partition data：

alter table <table name> truncate partition <partition name>;

 

Merge partition：合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限

ALTER TABLE <table name> MERGE PARTITIONS <partition name>,<partition name> INTO PARTITION <partition name>;

 

Split partition：拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE <table name> SPLIT PARTITION <partition name> AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION <partition name>,PARTITION <partition name>);

 

Partition search：

select * from <table_name> partitin<partition_name>;

select * from sales_history partition(sal_1);

 

Over partition search：

select sum( *) from

(select count(*) cn from t_table_SS PARTITION (P200709_1)

union all

select count(*) cn from t_table_SS PARTITION (P200709_2)

);

 

Check the number of partition：

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

 

Check partition table：

select * from DBA_PART_TABLES

 

Create global index：oracle会对主键自动创建全局索引

create index <index name> on <table name>(<column name>) global;

 

Create partition index：

create index <index name> on <table name>(<column name>) local;

 

Rebuild global index：

Alter index rebuild <index name> [online nologging]

 

Rebuild partition index：

Alter index <index name> rebuild partition <partition name> [online nologging]