时间范围分区拆分
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