Oracle11新特性——分区功能增强（四）

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 表达式