Oracle11新特性——分割槽功能增強（四）

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g在分割槽方面做了很大的提高，不但新增了4種複合分割槽型別，還增加了虛擬列分割槽、系統分割槽、INTERVAL分割槽等功能。

這一篇介紹Oracle11g的虛擬列分割槽功能。

Oracle11新特性——分割槽功能增強（一）：http://yangtingkun.itpub.net/post/468/403962

Oracle11新特性——分割槽功能增強（二）：http://yangtingkun.itpub.net/post/468/404223

Oracle11新特性——分割槽功能增強（三）：http://yangtingkun.itpub.net/post/468/404694

Oracle11g新增了虛擬列功能，虛擬列的值從其他的列推導而來，Oracle只儲存源資料，這個列不佔儲存空間。虛擬列其中一個引申功能就是虛擬列分割槽功能。

關於虛擬列的詳細描述，可以參考：http://yangtingkun.itpub.net/post/468/409211

11g增加對虛擬列的支援，這使得分割槽功能更加靈活。

舉一個簡單的例子，表中有一個日期列，希望根據日期列進行分割槽，每個月份一個分割槽，一年12個分割槽。這樣無論就可以迴圈使用這些分割槽。

具體例子如下：

SQL> CREATE TABLE T_PARTITION_MONTH
2 (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE)
3 PARTITION BY LIST (TO_NUMBER(TO_CHAR(CREATE_DATE, 'MM')))
4 (
5 PARTITION P1 VALUES (1),
6 PARTITION P2 VALUES (2),
7 PARTITION P3 VALUES (3),
8 PARTITION P4 VALUES (4),
9 PARTITION P5 VALUES (5),
10 PARTITION P6 VALUES (6),
11 PARTITION P7 VALUES (7),
12 PARTITION P8 VALUES (8),
13 PARTITION P9 VALUES (9),
14 PARTITION P10 VALUES (10),
15 PARTITION P11 VALUES (11),
16 PARTITION P12 VALUES (12)
17 );
PARTITION BY LIST (TO_NUMBER(TO_CHAR(CREATE_DATE, 'MM')))
*第 3 行出現錯誤:
ORA-00907: 缺失右括號

無論是採用範圍分割槽，還是列表分割槽，都要面臨分割槽列是一個函式表示式的問題。在11g以前，解決的唯一方法是人為新增一個列，取值為TO_NUMBER(TO_CHAR(CREATE_DATE), ‘MM’))，然而這種方法對程式影響比較大，而且會增加額外的儲存開銷。

在11g中，可以使用虛擬列分割槽來解決這個問題：

SQL> CREATE TABLE T_PARTITION_MONTH
2 (
3 ID NUMBER,
4 NAME VARCHAR2(30),
5 CREATE_DATE DATE,
6 PARTITION_MONTH AS (TO_NUMBER(TO_CHAR(CREATE_DATE, 'MM')))
7 )
8 PARTITION BY LIST (PARTITION_MONTH)
9 (
10 PARTITION P1 VALUES (1),
11 PARTITION P2 VALUES (2),
12 PARTITION P3 VALUES (3),
13 PARTITION P4 VALUES (4),
14 PARTITION P5 VALUES (5),
15 PARTITION P6 VALUES (6),
16 PARTITION P7 VALUES (7),
17 PARTITION P8 VALUES (8),
18 PARTITION P9 VALUES (9),
19 PARTITION P10 VALUES (10),
20 PARTITION P11 VALUES (11),
21 PARTITION P12 VALUES (12)
22 );

表已建立。

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T_PARTITION_MONTH';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_PARTITION_MONTH P1
T_PARTITION_MONTH P2
T_PARTITION_MONTH P3
T_PARTITION_MONTH P4
T_PARTITION_MONTH P5
T_PARTITION_MONTH P6
T_PARTITION_MONTH P7
T_PARTITION_MONTH P8
T_PARTITION_MONTH P9
T_PARTITION_MONTH P10
T_PARTITION_MONTH P11
T_PARTITION_MONTH P12

已選擇12行。

建立分割槽後，虛擬列的定義就不能在進行修改了：

SQL> ALTER TABLE T_PARTITION_MONTH
2 MODIFY PARTITION_MONTH AS (ROUND(TO_NUMBER(TO_CHAR(CREATE_DATE, 'MM'))));
MODIFY PARTITION_MONTH AS (ROUND(TO_NUMBER(TO_CHAR(CREATE_DATE, 'MM'))))
*第 2 行出現錯誤:
ORA-54019: 無法更改虛擬列表示式, 因為它是分割槽列

而且虛擬列分割槽只支援系統函式，不支援使用者定義的函式：

SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER DETERMINISTIC AS
2 BEGIN
3 RETURN 1;
4 END;
5 /

函式已建立。

SQL> CREATE TABLE T_PARTITION_VIRTUAL
2 (
3 ID NUMBER,
4 NAME VARCHAR2(30),
5 V_ID AS (F_TEST(ID))
6 )
7 PARTITION BY HASH(V_ID) PARTITIONS 4;
V_ID AS (F_TEST(ID))
*第 5 行出現錯誤:
ORA-54021: 不能在分割槽列或子分割槽列中使用 PL/SQL 表示式

其實不止是使用者自定義的函式，所有非STANDARD包中的函式都是不能用作虛擬分割槽列的：

SQL> CREATE TABLE T_PARTITION_VIRTUAL
2 (
3 ID NUMBER,
4 NAME VARCHAR2(30),
5 V_ID AS (DBMS_UTILITY.GET_HASH_VALUE(NAME, 1, 65536))
6 )
7 PARTITION BY HASH(V_ID) PARTITIONS 4;
V_ID AS (DBMS_UTILITY.GET_HASH_VALUE(NAME, 1, 65536))
*第 5 行出現錯誤:
ORA-54021: 不能在分割槽列或子分割槽列中使用 PL/SQL 表示式