在上幾篇文章中學習了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

oracle list partition列表分割槽(一)

相關文章