partition table(2)

来源:这里教程网 时间:2026-03-02 10:11:58 作者:

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:

Range

Partition

Hash

Partition

Add

Partition

组合

Partition

增加分区

Add partition

Add partition

Add partition

Add partition

合并分区

Coalesce

Partition

Modify partition

Coalesce

Subpartition…

删除分区

Drop partition

Drop

Partition

Drop partition

交换分区

Exchange

Partition

Exchange

Partition

Exchange

Partition

Exchange

Partition

Exchange

Subpartition

融合分区

Merge partitions

Merge

Partitions

Merge

partitions

修改分区增加值

Modify partition

Add values

修改分区删除值

Modify

Partition

Drop values

移动分区

Move partition

Move partition

Move partition

Move partition

更名分区

Rename partition

Rename partition

Rename partition

Rename partition

分割分区

Split partition

Splite partition

Split partition

舍弃分区

Truncate partition

Truncate partition

Truncate partition

Truncate partition

Truncate

subpartition

增加分区

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,需要重建indexAlter 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;

[@more@]

相关推荐