深入解析partition-list 分区

List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for  each partition. This is different from range partitioning, where a range of values is  

associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group  and organize unordered and unrelated sets of data in a natural way.

Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of  a single column of the table. 

The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to  any other partition do not generate an error.

由此可见，list分区需明确指定列值，不同于range分区只需指定列值范围即可。且不同于hash分区，因为明确指定的分区值，能控制每条记录存储在哪个分区。另外，它的分区列只能有一个，而不能像range或hash分区那样同时指定多个列做为分区依赖列。

若插入的列值不在指定的list分区范围内就会报错，为避免这种情况，使用list分区时可创建一个default分区。default 分区用来存储那些不在指定范围内的记录，类似于range分区的maxvalue分区。

1、创建list partition

语法如下：

需要我们指定的有：

column: 分区依赖列（只能是一个） ;

partition: 分区名称 ;

literal: 分区对应值（每个分区可以对应多个值） ;

tablespace_clause: 分区的存储属性，例如所在表空间等属性 ( 可为空 ) ，默认继承基表所在表空间的属性。

例：

SQL> edit

已写入 file afiedt.buf

  1 create table t_partition_list(id number,name varchar2(20))

  2 partition by list(id)(

  3 partition t_list_p1 values(1,3,5,7,9) tablespace tbs01,

  4 partition t_list_p2 values(2,4,6,8,10) tablespace tbs02,

  5 partition t_list_p3 values(21,23,25,27,29) tablespace tbs03,

  6* partition t_list_default values(default) tablespace tbs03)

SQL> /

表已创建。

SQL> select partition_name,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT TBS03

T_LIST_P1 TBS01

T_LIST_P2 TBS02

T_LIST_P3 TBS03

2、list分区表上创建索引

list分区与range和hash分区不同，它不能创建global分区索引，只能创建local分区索引，如下例所示：

SQL> edit

已写入 file afiedt.buf

  1 create index idx_t_part_list_id on t_partition_list(id)

  2 global partition by list(id)(

  3 partition i_list_p1 values(1,3,5,7,9) tablespace tbs01,

  4 partition i_list_p2 values(2,4,6,8,10) tablespace tbs02,

  5 partition i_list_p3 values(21,23,25,27,29) tablespace tbs03,

  6* partition i_list_default values(default) tablespace tbs03)

SQL> /

global partition by list(id)(

                    *

第 2 行出现错误:

ORA-14151: 无效的表分区方法

SQL> create index idx_t_part_list_id on t_partition_list(id) local;

索引已创建。

SQL> select partition_name,tablespace_name from user_ind_partitions

  2 where index_name='T_PARTITION_LIST';

未选定行

SQL> EDIT

已写入 file afiedt.buf

  1 select partition_name,tablespace_name from user_ind_partitions

  2* where index_name='IDX_T_PART_LIST_ID'

SQL> /

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT TBS03

T_LIST_P1 TBS01

T_LIST_P2 TBS02

T_LIST_P3 TBS03

list分区的local分区索引与前二篇提到的range和hash的local分区索引一样，其分区形式完全依赖于所属表的分区形wv式。

3、list分区表的管理

3.1、添加表分区（add partition）

语法：alter table tbname add partition values……

当list分区存在default分区时，add partition会报错，此时只能使用split分区代替。例：

SQL> edit

已写入 file afiedt.buf

  1 select partition_name,high_value,tablespace_name from user_tab_partitions

  2* where table_name='T_PARTITION_LIST'

SQL> /

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------------------------------------

T_LIST_DEFAULT default TBS03

T_LIST_P1 1, 3, 5, 7, 9 TBS01

T_LIST_P2 2, 4, 6, 8, 10 TBS02

T_LIST_P3 21, 23, 25, 27, 29 TBS03

SQL> alter table t_partition_list add partition values(20,22,24);

alter table t_partition_list add partition values(20,22,24)

            *

第 1 行出现错误:

ORA-14323: 在 DEFAULT 分区已存在时无法添加分区

3.2、分隔表分区（split partition）

语法：alter table tbname split partition ptname values(v1,v2……vn) into(partition newpt1 tbs_clause,partition newpt2 tbs_clause); 

split partition是将一个分区拆分成二个，list分区中最常用来拆分default分区，如下例所示：

SQL> alter table t_partition_list split partition t_list_default values(20,22,24)

  2 into(partition t_list_p4,partition t_list_default);

表已更改。

SQL> select partition_name,high_value,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------------------------------------

T_LIST_DEFAULT default TBS03

T_LIST_P1 1, 3, 5, 7, 9 TBS01

T_LIST_P2 2, 4, 6, 8, 10 TBS02

T_LIST_P3 21, 23, 25, 27, 29 TBS03

T_LIST_P4 20, 22, 24 TBS03

3.3、删除表分区（drop partition）

删除分区：alter table[tablename] drop partition[ptname];

删除子分区：alter table[tablename] drop subpartition[ptname]; 

例：

--删除default分区

SQL> alter table t_partition_list drop partition t_list_default;

表已更改。

SQL> select partition_name,high_value,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------------------------------------

T_LIST_P1 1, 3, 5, 7, 9 TBS01

T_LIST_P2 2, 4, 6, 8, 10 TBS02

T_LIST_P3 21, 23, 25, 27, 29 TBS03

T_LIST_P4 20, 22, 24 TBS03

--添加default分区

SQL> alter table t_partition_list add partition t_list_default values(default) tablespace tbs03;

表已更改。

3.4、合并表分区（merge partitions）

语法： alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;  

例：

SQL> select partition_name,high_value,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------------------------------------

T_LIST_DEFAULT default TBS03

T_LIST_P1 1, 3, 5, 7, 9 TBS01

T_LIST_P2 2, 4, 6, 8, 10 TBS02

T_LIST_P3 21, 23, 25, 27, 29 TBS03

T_LIST_P4 20, 22, 24 TBS03

SQL> alter table t_partition_list merge partitions t_list_p4,t_list_default into partition t_list_de fault; 

表已更改。

SQL> select partition_name,high_value,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

------------------------------ ---------------------------------------------------------------------

T_LIST_DEFAULT default JJJG

T_LIST_P1 1, 3, 5, 7, 9 TBS01

T_LIST_P2 2, 4, 6, 8, 10 TBS02

T_LIST_P3 21, 23, 25, 27, 29 TBS03

3.5、交换表分区（exchange partition）

 语法：alter table tbname1 exchange partition/subpartition ptname with table tbname2;

注意事项同前面二章（range与hash分区）此功能一样，在此不多述，具体用法参见下例。

--insert into list partition 

SQL> insert into t_partition_list values(1,'a');

已创建 1 行。

SQL> insert into t_partition_list values(2,'b');

已创建 1 行。

SQL> insert into t_partition_list values(33,'c');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_partition_list;

        ID NAME

---------- --------------------

         1 a

         2 b

        33 c

SQL> select * from t_partition_list partition(t_list_p2);

        ID NAME

---------- --------------------

         2 b

--在此借用range patition中exchange partition实例中的t_range_partition_tmp表，进行list partiiton的exchange partition操作

SQL> select * from t_partition_range_tmp;

        ID NAME

---------- --------------------

        11 a

SQL> insert into t_partition_range_tmp values(4,'c');

已创建 1 行。

SQL> insert into t_partition_range_tmp values(6,'d');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_partition_range_tmp;

        ID NAME

---------- --------------------

        11 a

         4 c

         6 d

SQL> alter table t_partition_list exchange partition t_list_p2

  2 with table t_partition_range_tmp;

with table t_partition_range_tmp

           *

第 2 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

SQL> delete from t_partition_range_tmp where id=11;

已删除 1 行。

SQL> commit;

提交完成。

SQL> alter table t_partition_list exchange partition t_list_p2

  2 with table t_partition_range_tmp;

表已更改。

SQL> select * from t_partition_list;

        ID NAME

---------- --------------------

         1 a

         4 c

         6 d

        33 c

SQL> select * from t_partition_range_tmp;

        ID NAME

---------- --------------------

         2 b

3.6、修改list表分区（modify partition）

3.6.1、add values添加指定分区的value值

此命令仅应用于 list 分区或 list 子分区，语法也非常简单：

Alter table tbname modify partition/subpartition ptname add values (v1,v2....vn);

例如：

SQL> select partition_name,high_value from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE

------------------------------ ----------------------------------------------------

T_LIST_DEFAULT default

T_LIST_P1 1, 3, 5, 7, 9

T_LIST_P2 2, 4, 6, 8, 10

T_LIST_P3 21, 23, 25, 27, 29

SQL> alter table t_partition_list modify partition t_list_p1 add values(11,12);

表已更改。

SQL> select partition_name,high_value from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE

------------------------------ ----------------------------------------------------

T_LIST_DEFAULT default

T_LIST_P1 1, 3, 5, 7, 9, 11, 12

T_LIST_P2 2, 4, 6, 8, 10

T_LIST_P3 21, 23, 25, 27, 29

注意： 要添加的新value值不能存在于当前任何分区中，并且当前表也不能存在记录值为新值的记录，特别是当你创建了default分区的时候，有必要先检查一下当前表不存在要添加的值，不 然执行会出错，如下例所示：

SQL> alter table t_partition_list modify partition t_list_p1 add values(2);

alter table t_partition_list modify partition t_list_p1 add values(2)

*

第 1 行出现错误:

ORA-14312: 值 2 已经存在于分区 2 中

SQL> alter table t_partition_list modify partition t_list_p1 add values(33);

alter table t_partition_list modify partition t_list_p1 add values(33)

            *

第 1 行出现错误:

ORA-14324: 所要添加的值已存在于 DEFAULT分区之中

--查询表t_partition_list记录，存在id为33的记录

SQL> select * from t_partition_list;

        ID NAME

---------- --------------------

         1 a

         4 c

         6 d

        33 c

3.6.2、drop values删除指定分区的value值

与上面的add values类似，只适用于list分区或list子分区，但功能相反，语法如下：

alter table tbname modify partition/subpartition ptname drop values(v1,v2……vn);

例：将上面例子中t_parition_list表t_list_p1分区中新增的11,12值删除

SQL> alter table t_partition_list modify partition t_list_p1 drop values(11,12);

表已更改。

SQL> select partition_name,high_value from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE

------------------------------ -------------------------------------------------

T_LIST_DEFAULT default

T_LIST_P1 1, 3, 5, 7, 9

T_LIST_P2 2, 4, 6, 8, 10

T_LIST_P3 21, 23, 25, 27, 29

注意：在删除list分区中的values值时，要确认当前分区存在指定的value值，且没有应用该值的记录，否则会报错，如下例所示：

SQL> alter table t_partition_list modify partition t_list_p1 drop values(2);

alter table t_partition_list modify partition t_list_p1 drop values(2)

*

第 1 行出现错误:

ORA-14313: 值 2 不在分区 T_LIST_P1 中

SQL> alter table t_partition_list modify partition t_list_p1 drop values(1);

alter table t_partition_list modify partition t_list_p1 drop values(1)

            *

第 1 行出现错误:

ORA-14518: 分区包含的某些行对应于已删除的值

3.7、截断表分区（truncate partition）

语法：alter table tbname truncate partition ptname

SQL> select * from t_partition_list;

        ID NAME

---------- --------------------

         1 a

         4 c

         6 d

        33 c

SQL> select partition_name,high_value from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME HIGH_VALUE

------------------------------ ----------------------------------------

T_LIST_DEFAULT default

T_LIST_P1 1, 3, 5, 7, 9

T_LIST_P2 2, 4, 6, 8, 10

T_LIST_P3 21, 23, 25, 27, 29

SQL> alter table t_partition_list truncate partition t_list_p1;

表被截断。

SQL> select * from t_partition_list;

        ID NAME

---------- --------------------

         4 c

         6 d

        33 c

3.8、移动表分区（move partition）

语法：alter table tbname move partition ptname ……

前二章讲range和hash分区时，都提到move partition，其用法功能都一样，在此不多述，直接看例子：

SQL> select partition_name,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT JJJG

T_LIST_P1 TBS01

T_LIST_P2 JJJG

T_LIST_P3 TBS03

SQL> alter table t_partition_list move partition t_list_p2 tablespace tbs02;

表已更改。

SQL> select partition_name,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT JJJG

T_LIST_P1 TBS01

T_LIST_P2 TBS02

T_LIST_P3 TBS03

3.9、重命名表分区（rename partition）

语法：alter table tbname rename partition ptname to newptname;

例：

SQL> select partition_name,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT JJJG

T_LIST_P1 TBS01

T_LIST_P2 TBS02

T_LIST_P3 TBS03

SQL> alter table t_partition_list rename partition t_list_p1 to t_list_p4;

表已更改。

SQL> select partition_name,tablespace_name from user_tab_partitions

  2 where table_name='T_PARTITION_LIST';

PARTITION_NAME TABLESPACE_NAME

------------------------------ ------------------------------

T_LIST_DEFAULT JJJG

T_LIST_P2 TBS02

T_LIST_P3 TBS03

T_LIST_P4 TBS01

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1119937/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1119937/