今天总结了一下分区表的知识

分享一下。

大体有以下内容：

1.hash partition

range partition

list partition

range hash partition

range list partitio

2.partition operations

add ,truncate,drop partitions

merge ,move ,split partitions

add values in list partitions

exchange partition

3.prefixed index,nonprefixed index

global index

4.分区的导入，导出

hash partition

SQL> CREATE TABLE HASH_PART (A NUMBER(10),B VARCHAR2(100))

这样默认只有一个分区

SQL> create table hash_part(a number(10),b varchar2(100)) tablespace tbs1

Table created.

SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';

TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME

8 rows selected.

TABLE_NAME TABLESPACE_NAME

指定partition 的存储

Table created.

SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';

TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4307 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from hash_part partition(part_01);

COUNT(*)

SQL> c/01/02

COUNT(*)

SQL> c/02/03

COUNT(*)

Table created.

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4313 rows created.

SQL> commit;

Commit complete.

SQL> insert into hash_part select object_id,object_name from all_objects where object_id is not null;

4313 rows created. --hash 键值不是主键

SQL> commit;

Commit complete.

SQL> select count(*) from hash_part partition(part_01);

COUNT(*)

SQL> c/01/02;

COUNT(*)

SQL> c/02/03

COUNT(*)

SQL> c/03/04

COUNT(*)

--不是严格意义上的条数的平均。

SQL> INSERT INTO LIST_PART VALUES(2,'B');

1 row created.

SQL> INSERT INTO LIST_PART VALUES(3,'C');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> UPDATE LIST_PART SET B='B' WHERE A=3;

--这种情况尤其要注意，因为改了值会影响分区，所以不支持这种操作。

--把表里的数据清空。

SQL> insert into list_part select object_id,decode(mod(object_id,3),1,'A',2,'B','C') B from all_objects where object_id is not null;

4308 rows created.

COUNT(*)

COUNT(*)

SQL> alter table list_part add partition part_05 values(default); -list partition需要加default分区

Table altered.

SQL> create table range_hash_part(a number(10),b varchar2(100))

Table created.

PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE

--range_list

TABLESPACE_NAME PARTITION_NAME SUBPARTITION_COUNT

Index created.

SQL> select count(*) from range_hash_part partition(part_01);

COUNT(*)

--rename partition

Table altered.

SQL> alter table range_hash_part rename partition part_011 to part_01;

Table altered.

SQL> --truncate partition

Table truncated.

--drop partition

Table altered.

SQL>

--move partition

TABLESPACE_NAME PARTITION_NAME

--for range_hash partition

SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME PARTITION_NAME

Table altered.

SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME PARTITION_NAME

--merge partition

Table altered.

TABLESPACE_NAME PARTITION_NAME

Table altered.

TABLESPACE_NAME PARTITION_NAME

--split partition

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

--for range partition tables

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

--for list partition tables

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

SQL> alter table list_part split partition part_02 values('D') into (partition part_06,partition part_07);

--这样来split不正确,有多个可选值才可以

--来模拟校正一下

SQL> alter table list_part merge partitions part_02,part_03 into partition part_02;

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

Table altered.

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

Table altered.

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

SQL> alter table list_part modify partition part_03 drop values('B');

SQL> c/'B'/'D'

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

SQL> create table exchange_test as select object_id,object_name from all_objects where object_id <3000;

Table created.

SQL> select count(*) from range_part partition(part_02);

COUNT(*)

--列名要求必须一致

Table dropped.

Table created.

1300 rows created.

SQL> alter table range_part exchange partition part_02 with table exchange_test

Table altered.

SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';

TABLESPACE_NAME HIGH_VALUE PARTITION_NAME

COUNT(*)

--查看交换后的partition的数量

SQL> select count(*) from range_part partition(part_02);

COUNT(*)

SQL> alter table range_part exchange partition part_02 with table exchange_test;

Table altered.

--当然直接插入值也可以

1300 rows created.

最后说一下分区的导入，导出

[oracle@oel1 ~]$ exp hr/hr tables=range_part:part_02 file=range_part_02.dmp

Export: Release 10.2.0.1.0 - Production on Wed Oct 17 20:37:26 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

About to export specified tables via Conventional Path ...

[oracle@oel1 ~]$ imp hr/hr tables=range_part:part_02 file=range_part_02 ignore=y

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Export file created by EXPORT:V10.02.01 via conventional path