分区表学习笔记

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

分享一下。

大体有以下内容：

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 <3000;

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 <3000
  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<3000<4000,所以对于小于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.