今天总结了一下分区表的知识
分享一下。
大体有以下内容：
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))
2 PARTITION BY HASH(B)
3 ;
这样默认只有一个分区
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
HASH_PART SYS_P21 TBS2
SQL> create table hash_part(a number(10),b varchar2(100)) tablespace tbs1
2 partition by hash(a)
3 partitions 8
4 store in (tbs2,tbs3,tbs4);
Table created.
SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';
TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME
-------------------- -------------------- ------------------------------
TBS2 HASH_PART SYS_P22
TBS3 HASH_PART SYS_P23
TBS4 HASH_PART SYS_P24
TBS2 HASH_PART SYS_P25
TBS3 HASH_PART SYS_P26
TBS4 HASH_PART SYS_P27
TBS2 HASH_PART SYS_P28
TBS3 HASH_PART SYS_P29
8 rows selected.
SQL> SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES WHERE TABLE_NAME='HASH_PART';
TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
HASH_PART
指定partition 的存储
SQL> create table hash_part(a number(10),b varchar2(100))
partition by hash(a)
(
partition part_01 tablespace tbs2,
partition part_02 tablespace tbs3,
partition part_03 tablespace tbs4，
);
Table created.
SQL> select tablespace_name,segment_name,partition_name from user_segments where segment_name='HASH_PART';
TABLESPACE_NAME SEGMENT_NAME PARTITION_NAME
-------------------- -------------------- ------------------------------
TBS3 HASH_PART PART_01
TBS4 HASH_PART PART_02
TBS5 HASH_PART PART_03
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(*)
----------
1045
SQL> c/01/02
1* select count(*) from hash_part partition(part_02)
SQL> /
COUNT(*)
----------
2148 --如果分区不是2的幂，则数据分区不均匀
SQL> c/02/03
1* select count(*) from hash_part partition(part_03)
SQL> /
COUNT(*)
----------
1114
--测试hash table partition 的数据平均情况
SQL> create table hash_part(a number(10),b varchar2(100))
partition by hash(a)
( partition part_01 tablespace tbs2,
partition part_02 tablespace tbs3,
partition part_03 tablespace tbs4,
partition part_04 tablespace tbs5
); 2 3 4 5 6 7
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(*)
----------
2094
SQL> c/01/02;
1* select count(*) from hash_part partition(part_02)
SQL> /
COUNT(*)
----------
2192
SQL> c/02/03
1* select count(*) from hash_part partition(part_03)
SQL> /
COUNT(*)
----------
2236
SQL> c/03/04
1* select count(*) from hash_part partition(part_04)
SQL> /
COUNT(*)
----------
2104
--不是严格意义上的条数的平均。
--List partition
SQL> create table list_part(a number(10),b varchar2(100))
2 partition by list(b)
3 (
4 partition part_01 values('A'),
5 partition part_02 values('B'),
6 partition part_03 values('C')
7 );
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;
UPDATE LIST_PART SET B='B' WHERE A=3
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
--这种情况尤其要注意，因为改了值会影响分区，所以不支持这种操作。
--把表里的数据清空。
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.
1* select count(*) from list_part where b='B'
SQL> /
COUNT(*)
----------
1466
SQL> select count(*) from list_part partition(part_02);
COUNT(*)
----------
1466
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))
2 partition by range(a)
3 subpartition by hash(b)
4 subpartitions 4 store in (tbs2,tbs3)
5 (
6 partition part_01 values less than(1000),
7 partition part_02 values less than(4000),
8 partition part_03 values less than (6000),
9 partition part_04 values less than(8000),
10 partition part_max values less than(maxvalue)
11 subpartitions 2 store in (tbs3,tbs4)
12 );
Table created.
select partition_name,subpartition_count,high_value from user_tab_partitions where table_name='RANGE_HASH_PART'
/
PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE
------------------------------ ------------------ --------------------
PART_01 4 1000
PART_02 4 4000
PART_03 4 6000
PART_04 4 8000
PART_MAX 2 MAXVALUE
SQL> create table list_hash_part(a number(10),b varchar2(100))
2 partition by list(b)
3 subpartition by hash(a)
4 subpartitions 4 store in (tbs2,tbs4)
5 (partition part_01 values('A'),
6 partition part_02 values('B'),
7 partition part_03 values('C'),
8 partition part_04 values(default)
9 subpartitions 2 store in(tbs3,tbs5)
10 );
subpartition by hash(a)
*
ERROR at line 3:
ORA-00922: missing or invalid option
--这种分区不存在
--range_list
create table range_list_part(a number(10),b varchar2(100))
partition by range(a)
subpartition by list(b)
(
partition part_01 values less than(1000)
(subpartition part_01_01 values('A'),
subpartition part_01_02 values('B'),
subpartition part_01_03 values('C'),
subpartition part_01_04 values(default)
),
partition part_02 values less than(4000)
(subpartition part_02_01 values('A'),
subpartition part_02_02 values('B'),
subpartition part_02_03 values('C'),
subpartition part_02_04 values(default)
),
partition part_03 values less than(maxvalue)
(subpartition part_03_01 values(default)
)
)
SQL> select tablespace_name,partition_name,subpartition_count from user_tab_partitions where table_name='RANGE_LIST_PART';
TABLESPACE_NAME PARTITION_NAME SUBPARTITION_COUNT
-------------------- ------------------------------ ------------------
TBS2 PART_01 4
TBS2 PART_02 4
TBS2 PART_03 1
global_index
create index glb_ind_range_list_part on range_list_part(a)
global partition by range(a)
(partition part_01 values less than (1000) tablespace tbs2,
partition part_02 values less than(5000) tablespace tbs3,
partition part_03 values less than(maxvalue) tablespace tbs4
)
--non prefixed index
SQL> create index ind_range_list_part_nopre on range_list_part(b,a);
Index created.
SQL> select count(*) from range_hash_part partition(part_01);
COUNT(*)
----------
66
--rename partition
SQL> alter table range_hash_part rename partition part_01 to part_011;
Table altered.
SQL> alter table range_hash_part rename partition part_011 to part_01;
Table altered.
SQL> --truncate partition
SQL> alter table range_hash_part truncate partition part_01;
Table truncated.
--drop partition
SQL> alter table range_hash_part drop partition part_01;
Table altered.
SQL>
--move partition
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_HASH_PART';
TABLESPACE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBS2 PART_02
TBS2 PART_03
TBS2 PART_04
TBS2 PART_MAX
--for range_hash partition
SQL> alter table range_hash_part move partition part_02 tablespace tbs3;
alter table range_hash_part move partition part_02 tablespace tbs3
*
ERROR at line 1:
ORA-14257: cannot move partition other than a Range or Hash partition
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';
TABLESPACE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBS2 PART_01
TBS2 PART_02
TBS2 PART_03
SQL> alter table range_part move partition part_01 tablespace tbs4;
Table altered.
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';
TABLESPACE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBS4 PART_01
TBS2 PART_02
TBS2 PART_03
TBS2 PART_04
--merge partition
SQL> alter table range_part merge partitions part_01,part_02;
Table altered.
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';
TABLESPACE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBS2 PART_03
TBS2 PART_04
TBS2 SYS_P48
--指定merge后的partition名字
SQL> alter table range_part merge partitions part_03,part_04 into partition part_05;
Table altered.
SQL> select tablespace_name,partition_name from user_tab_partitions where table_name='RANGE_PART';
TABLESPACE_NAME PARTITION_NAME
------------------------------ ------------------------------
TBS2 PART_05
TBS2 SYS_P48
--split partition
SQL> /
TABLESPACE_NAME HIGH_VALUE PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2 4000 PART_01
TBS2 MAXVALUE PART_05
--for range partition tables
SQL> alter table range_part split partition part_01 at (2000) into (partition part_01,partition part_02);
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
------------------------------ -------------------- --------------------
TBS2 2000 PART_01
TBS2 4000 PART_02
TBS2 MAXVALUE PART_05
--for list partition tables
SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='LIST_PART';
TABLESPACE_NAME HIGH_VALUE PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2 'A' PART_01
TBS2 'B' PART_02
TBS2 'C' PART_03
TBS2 default PART_05
SQL> alter table list_part split partition part_02 values('D') into (partition part_06,partition part_07);
alter table list_part split partition part_02 values('D') into (partition part_06,partition part_07)
*
ERROR at line 1:
ORA-14314: resulting List partition(s) must contain atleast 1 value
--这样来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
------------------------------ -------------------- --------------------
TBS2 'A' PART_01
TBS2 'C', 'B' PART_02
TBS2 default PART_05
--这样list partition就可以split了
SQL> alter table list_part split partition part_02 values('C') into (partition part_02,partition part_03);
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
------------------------------ -------------------- --------------------
TBS2 'A' PART_01
TBS2 'C' PART_02
TBS2 'B' PART_03
TBS2 default PART_05
--add values to list partition
SQL> alter table list_part modify partition part_03 add values('D');
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
------------------------------ -------------------- --------------------
TBS2 'A' PART_01
TBS2 'C' PART_02
TBS2 'B', 'D' PART_03
TBS2 default PART_05
SQL> alter table list_part modify partition part_03 drop values('B');
alter table list_part modify partition part_03 drop values('B')
*
ERROR at line 1:
ORA-14518: partition contains rows corresponding to values being dropped
--如果parition对应的values下有数据，则drop values会失败
SQL> c/'B'/'D'
1* alter table list_part modify partition part_03 drop values('D')
SQL> /
Table altered.
--exchange partition
SQL> select tablespace_name,high_value,partition_name from user_tab_partitions where table_name='RANGE_PART';
TABLESPACE_NAME HIGH_VALUE PARTITION_NAME
------------------------------ -------------------- --------------------
TBS2 2000 PART_01
TBS2 4000 PART_02
TBS2 MAXVALUE PART_05
SQL> create table exchange_test as select object_id,object_name from all_objects where object_id
Table created.
SQL> select count(*) from range_part partition(part_02);
COUNT(*)
----------
1256
SQL> alter table range_part exchange partition part_02 with table exchange_test;
alter table range_part exchange partition part_02 with table exchange_test
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
--列名要求必须一致
--删掉重新模拟
SQL> drop table exchange_test;
Table dropped.
SQL> create table exchange_test (a number(10),b varchar2(100));
Table created.
SQL> insert into exchange_test select object_id ,object_name from all_objects where object_id
2 /
1300 rows created.
SQL> alter table range_part exchange partition part_02 with table exchange_test;
alter table range_part exchange partition part_02 with table exchange_test
*
ERROR at line 1:
ORA-14099: all rows in table do not qualify for specified partition
--默认会校验值的有效性，2000
SQL> alter table range_part exchange partition part_02 with table exchange_test
2 without validation;
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
------------------------------ -------------------- --------------------
TBS2 2000 PART_01
TBS2 4000 PART_02
TBS2 MAXVALUE PART_05
--没有变化
--查看exchange 后的数据量
SQL> select count(*) from exchange_test;
COUNT(*)
----------
1256 --和交换前partition part_02的条数一致
--查看交换后的partition的数量
SQL> select count(*) from range_part partition(part_02);
COUNT(*)
----------
1300 -和建表时的数据条数一致
--再交换回来
SQL> alter table range_part exchange partition part_02 with table exchange_test;
Table altered.
--当然直接插入值也可以
SQL> insert into range_part select * from exchange_test;
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.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table RANGE_PART
. . exporting partition PART_02 1256 rows exported
Export terminated successfully without warnings.
[oracle@oel1 ~]$ imp hr/hr tables=range_part:part_02 file=range_part_02 ignore=y
Import: Release 10.2.0.1.0 - Production on Wed Oct 17 20:40:32 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into HR
. importing HR's objects into HR
. . importing partition "RANGE_PART":"PART_02" 1256 rows imported
Import terminated successfully without warnings.