partition table(2)

5).interval分区(11g新增)

Interval分区是range分区的一种加强版本。可以实现equi-sized范围分区的自动化。随着数据的增加会分配更多的分区，并自动创建新的分区和本地索引。

create table part6

(id int,

name varchar2(20),

dt date

)

partition by range(dt)

interval (numtoyminterval(1,'month'))

(

partition p1 values less than(to_date('2012/01/01','yyyy/mm/dd'))

)

6).外键分区(11g新增)

SQL> create table part1

2 (id int,name varchar2(10))

3 partition by range(id)

4 (partition p1 values less than (100),

5 partition p2 values less than (200),

6 partition p3 values less than (maxvalue)

7 );

SQL> alter table part1 add primary key(id);

Table altered.

SQL> create table part7

2 (id int not null, à这个必须要，否则出现ora-14652

3 name varchar2(20),

4 constraint part7_fk foreign key(id) references part1(id)

5 )

6 partition by reference (part7_fk)

7 /

Table created.

7).虚拟列分区

11g以前的版本，只有分区键存在于表中才可以对该表实施分区操作。而11g则不需要。

create table part3

(id int,

name varchar2(20),

id1 int generated always as (to_number(substr(to_char(id),1,1)))

)

partition by list(id1)

( partition p1 values (3),

partition p2 values (5),

partition p3 values (default)

)

8).表分区的管理

Range partition:

增加partition:

增加分区

SQL> alter table part1 add partition p3 values less than (to_date('2014/01/01','yyyy/mm/dd')) [tablespace xxx];

已更改表格.

SQL> alter table part1 add partition p4 values less than (maxvalue) [tablespace xxx];

合并分区(hash partition)

SQL> alter table part_hash coalesce partition;

SQL> alter table part5 modify partition p1 coalesce subpartition;

删除分区

SQL> alter table part1 drop partition p3;

如果有全局index，删除partition之后，index会标识为unusable，需要重建index。Alter index xxx rebuild

如果有约束：alter table xxx disable constraints cons_xxx

Alter table xxxx drop partition px;

Alter table xxx enable constraints cons_xxx

融合分区

create index part1_index on part1(id,name)

local

(partition p1,

partition p2,

partition p3,

partition p4

)

SQL> alter table part1 merge partitions p3,p4 into partition p5;

索引变成无效

SQL> select status from user_indexes where index_name='PART1_INDEX'

2 ;

STATUS

----------------

N/A

SQL> alter table part1 modify partition p5 rebuild unusable local indexes;