時間範圍分割槽拆分
create table emp (
id number(6) not null,
hire_date date not null)
partition by range(hire_date)
(partition p_1998 values less than (to_date('1998-12-31','YYYY-MM-DD')),
partition p_1999 values less than (to_date('1999-12-31','YYYY-MM-DD')),
partition p_default values less than (maxvalue)
);

insert into emp values(10,to_date('1999-5-20','YYYY-MM-DD'));
insert into emp values(20,to_date('1999-8-10','YYYY-MM-DD'));

SQL> select count(*) from emp partition(p_1999);
COUNT(*)
----------
2
將p_1999分割槽拆分成兩個分割槽

SQL> ALTER TABLE emp SPLIT PARTITION p_1999
2 AT (to_date('1999-07-01','YYYY-MM-DD'))
3 INTO (PARTITION p_1999_01, PARTITION p_1999_02);

表已更改。

SQL> select count(*) from emp partition(p_1999_01);

COUNT(*)
----------
1

SQL> select count(*) from emp partition(p_1999_02);

COUNT(*)
----------
1

將p_default分割槽拆分成兩個分割槽

SQL> insert into emp values(30,to_date('2000-5-27','YYYY-MM-DD'));

已建立 1 行。

SQL> insert into emp values(40,to_date('2001-10-02','YYYY-MM-DD'));

已建立 1 行。

SQL> select count(*) from emp partition(p_default);

COUNT(*)
----------
2

SQL> ALTER TABLE emp SPLIT PARTITION p_default
2 AT (to_date('2000-12-31','YYYY-MM-DD'))
3 INTO (PARTITION p_2000, PARTITION p_default);

表已更改。

SQL> select count(*) from emp partition(p_default);

COUNT(*)
----------
1

SQL> select count(*) from emp partition(p_2000);

COUNT(*)
----------
1

SQL> select * from emp partition(p_default);

ID HIRE_DATE
---------- -------------------
40 2001-10-02 00:00:00

SQL> select * from emp partition(p_2000);

ID HIRE_DATE
---------- -------------------
30 2000-05-27 00:00:00

LIST分割槽拆分
SQL> create table dept (
2 id number(6) not null,
3 area varchar2(15) not null)
4 partition by list(area)
5 (
6 PARTITION asia VALUES ('CHINA', 'THAILAND'),
7 PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
8 PARTITION west VALUES ('AMERICA'),
9 PARTITION east VALUES ('INDIA'),
10 PARTITION rest VALUES (DEFAULT));

表已建立。

SQL> insert into dept values(10,'MEXICO');
已建立 1 行。

SQL> insert into dept values(20,'COLOMBIA');
已建立 1 行。

SQL> select count(*) from dept partition(rest);

COUNT(*)
----------
2

SQL> ALTER TABLE dept SPLIT PARTITION rest
2 VALUES ('MEXICO', 'COLOMBIA')
3 INTO (PARTITION south, PARTITION rest);
表已更改。

SQL> select count(*) from dept partition(rest);

COUNT(*)
----------
0

SQL> select count(*) from dept partition(south);

COUNT(*)
----------
2

分割槽合併

SQL> SELECT table_name,partition_name FROM USER_tab_partitions where table_name='EMP';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP P_1998
EMP P_1999_01
EMP P_1999_02
EMP P_2000
EMP P_DEFAULT

SQL> ALTER TABLE emp
2 MERGE PARTITIONS P_1999_01, P_1999_02 INTO PARTITION P_1999;

表已更改。

SQL> SELECT table_name,partition_name FROM USER_tab_partitions where table_name='EMP';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP P_1998
EMP P_1999
EMP P_2000
EMP P_DEFAULT

SQL> SELECT table_name,partition_name FROM USER_tab_partitions where table_name='DEPT';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT ASIA
DEPT EUROPE
DEPT WEST
DEPT EAST
DEPT SOUTH
DEPT REST
已選擇6行。

SQL> ALTER TABLE dept
2 MERGE PARTITIONS south, rest INTO PARTITION rest;
表已更改。

SQL> select count(*) from dept partition(rest);

COUNT(*)
----------
2

Oracle 11g 分割槽拆分與合併

相關文章