oracle list 分区详解,oracle list partition列表分区(一)

在上几篇文章中学习了range partition及hash partition的相关知识;

本文测试list partition的用法:

内容包括:

1,创建语法

2,操作维护

3,操作限制

----1,创建语法

---最简朴创建list partition语法

SQL> create table t_list_partition(a int,b int)

2  partition by list(a)

3  (partition p1 values (1,2,3,4,5),

4   partition p2 values (6,7,8,9,10)

5  )

6  /

Table created.

-----2,操作维护

---测试default参数,须在表列中指定default值

SQL> create table t_list_partition(a int default 5,b int)

2  partition by list(a)

3  (partition p1 values (1,2,3,4,5),

4   partition p2 values (6,7,8,9,10),

5   partition p3 values (default)

6  )

7  /

Table created.

SQL> select * from t_list_partition;

no rows selected

SQL> insert into t_list_partition values(1,1);

1 row created.

SQL> insert into t_list_partition values(5,5);

1 row created.

SQL> insert into t_list_partition values(default,5);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_list_partition;

A          B

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

1          1

5          5

5          5

SQL> select * from t_list_partition partition(p1);

A          B

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

1          1

5          5

5          5

---测试说明:如list partition分区的default与之前分区的列表值相同,则不会向此分区插入数据,优先插入到之前的列表分区

--oracle在创建时不会检查分区各个值是相互逻辑矛盾

SQL> select * from t_list_partition partition(p3);

no rows selected

---查询分区,还是3个分区

SQL> select table_name,partition_name,high_value from user_tab_partitions where

2  table_name='T_LIST_PARTITION';

TABLE_NAME

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

PARTITION_NAME

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

HIGH_VALUE

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

T_LIST_PARTITION

P1

1, 2, 3, 4, 5

T_LIST_PARTITION

P2

6, 7, 8, 9, 10

TABLE_NAME

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

PARTITION_NAME

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

HIGH_VALUE

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

T_LIST_PARTITION

P3

default

---用正确值指定default值

SQL> create table t_list_partition(a int default 11,b int)

2  partition by list(a)

3  (partition p1 values (1,2,3,4,5),

4   partition p2 values (6,7,8,9,10),

5   partition p3 values (default)

6  )

7  /

Table created.

SQL> select * from t_list_partition;

no rows selected

SQL> insert into t_list_partition values(1,1);

1 row created.

SQL> insert into t_list_partition values(6,1);

1 row created.

SQL> insert into t_list_partition values(default,1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_list_partition;

A          B

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

1          1

6          1

11          1

SQL> select * from t_list_partition partition(p1);

A          B

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

1          1

SQL> select * from t_list_partition partition(p2);

A          B

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

6          1

---default值创建的记录会映射到第3分区

SQL> select * from t_list_partition partition(p3);

A          B

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

11          1

----如为列表值指定null,不再测试

----列出结果:null指在哪个分区,记录映射到哪个分区中

-----3,操作限制

---列表分区分区列仅一个列

You can specify only one partitioning key column.

--分区列的类型仅为如下类型

The partitioning key column must be of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, VARCHAR, NUMBER, FLOAT, DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIMEZONE, or RAW.

QL> create table t_list_partition(a int,b int)

2  partition by list(a,b)

3  (partition p1 values (1,2,3,4,5,null),

4   partition p2 values (6,7,8,9,10)

5  )

6  /

partition p1 values (1,2,3,4,5,null),

RROR at line 3:

RA-14304: List partitioning method expects a single partitioning column