分區(qū)表的基本操作

分區(qū)表的基本操作，簡單記錄一下~~~ 實例： 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

分區(qū)表的基本操作，簡單記錄一下~~~

實例：

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、分區(qū)清除，分區(qū)刪除

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、增加分區(qū)

alter table [partiontion_tablename] add partition [partitionname] values less than (120000);

在分區(qū)有maxvalue時會報一下錯誤，需要先把maxvalue分區(qū)的數(shù)據(jù)分區(qū)交換到一個中間表，drop 掉該分區(qū)之后，新建分區(qū)，最后把改中間表的數(shù)據(jù)重新插入該分區(qū)表。

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、分區(qū)交換：

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表的數(shù)據(jù)交換到p12分區(qū)中。

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、分區(qū)切割

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、分區(qū)合并

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

本條技術(shù)文章來源于互聯(lián)網(wǎng)，如果無意侵犯您的權(quán)益請點擊此處反饋版權(quán)投訴

本文系統(tǒng)來源：php中文網(wǎng)

總結(jié)

以上是生活随笔為你收集整理的php分区表,分区表的基本操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。