分区表的基本操作

分区表的基本操作，简单记录一下~~~ 实例： SQL create table t_part (id int,col2 int,col3 int) 2 partition by range (id) 3 ( 4 partition p1 values less than (10000), 5 partition p2 values less than (20000), 6 partition p3 values less than (300

分区表的基本操作，简单记录一下~~~
实例：
SQL> create table t_part (id int,col2 int,col3 int)
2 partition by range (id)
3 (
4 partition p1 values less than (10000),
5 partition p2 values less than (20000),
6 partition p3 values less than (30000),
7 partition p4 values less than (40000),
8 partition p5 values less than (50000),
9 partition p6 values less than (60000),
10 partition p7 values less than (70000),
11 partition p8 values less than (80000),
12 partition p9 values less than

(90000),
13 partition p10 values less than (100000),
14 partition p11 values less than (110000),
15 partition p12 values less than (maxvalue)
16 );

Table created.

SQL> insert into t_part select rownum,rownum+1,rownum+2 from dual connect by rownum < 150000;

149999 rows created.

SQL> commit;

Commit complete.

1、分区清除，分区删除
alter table [partiontion_tablename] drop/truncate partition [partitionname];

SQL> alter table t_part drop partition p1;

Table altered.

SQL> alter table t_part truncate partition p2;

Table truncated.

2、增加分区
alter table [partiontion_tablename] add partition [partitionname] values less than (120000);
在分区有maxvalue时会报一下错误，需要先把maxvalue分区的数据分区交换到一个中间表，drop 掉该分区之后，新建分区，最后把改中间表的数据重新插入该分区表。
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SQL> alter table t_part add partition p12 values less than (120000);

Table altered.

3、分区交换：
alter table [partiontion_tablename] exchange partition [partitionname] with table [tablename];

SQL> create table t_norm (id int,col2 int,col3 int);

Table created.

SQL> select count(*) from t_part partition(p12);

COUNT(*)
———-
40000

SQL> alter table t_part exchange partition p12 with table t_norm;

Table altered.

SQL> select count(*) from t_part partition(p12);

COUNT(*)
———-
0

SQL> select count(*) from t_norm;

COUNT(*)
———-
40000

同理，我们可以把t_norm表的数据交换到p12分区中。
SQL> alter table t_part exchange partition p12 with table t_norm;

Table altered.

SQL> select count(*) from t_part partition(p12);

COUNT(*)
———-
40000

SQL> select count(*) from t_norm;

COUNT(*)
———-
0

4、分区切割
alter table [partiontion_tablename] split partition [partitionname] at ([values]) into (partition [partitionname1],partition [partitionname2]); –partitionname1,2是一分为二后新的名字

SQL> alter table t_part rename partition p12 to pmax;

Table altered.

SQL> select count(*) from t_part partition (pmax);

COUNT(*)
———-
40000

SQL> alter table t_part split partition pmax at (120000) into (partition p12,partition pmax);

Table altered.

SQL> select count(*) from t_part partition(p12);

COUNT(*)
———-
10000

SQL> select count(*) from t_part partition (pmax);

COUNT(*)
———-
30000

5、分区合并
alter table [partiontion_tablename] merge partitions [partitionname1],[partitionname2] into partition [partitionname_new];

SQL> alter table t_part merge partitions p12,pmax into partition pmax;

Table altered.

SQL> select count(*) from t_part partition (pmax);

COUNT(*)
———-
40000

喜欢 (0)赏

[搞代码]

分享 (0)