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

 

 

 

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

转载于:http://blog.itpub.net/9240380/viewspace-752297/