oracle表分区

打算写一系列的文章介绍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 表达式

=============================================================


-- Create table
--表T_IISS_LOG_COMMONUSERLOG
create table T_IISS_LOG_COMMONUSERLOG
(
GID NUMBER(38) not null,
LOGDATE VARCHAR2(30) not null,
OFFDATE VARCHAR2(30),
USERID VARCHAR2(38) not null,
USERTOKEN VARCHAR2(64) not null,
CLIENTCODE VARCHAR2(30),
PORTALTYPE NUMBER(4),
AREACODE VARCHAR2(30),
IP VARCHAR2(30),
LOGINIPADDR VARCHAR2(30),
CLIENTVERSION VARCHAR2(32),
GWUA VARCHAR2(512),
PAGEAREACODE VARCHAR2(32),
LOGINTYPE VARCHAR2(4),
GATEWAY VARCHAR2(4),
ENTERDATE DATE default sysdate,
PARTITION_MONTH AS (to_number(to_char(ENTERDATE,'MM')))
)
PARTITION BY LIST (PARTITION_MONTH)
(
PARTITION COMMONUSERLOG_PAR_01 VALUES (1),
PARTITION COMMONUSERLOG_PAR_02 VALUES (2),
PARTITION COMMONUSERLOG_PAR_03 VALUES (3),
PARTITION COMMONUSERLOG_PAR_04 VALUES (4),
PARTITION COMMONUSERLOG_PAR_05 VALUES (5),
PARTITION COMMONUSERLOG_PAR_06 VALUES (6),
PARTITION COMMONUSERLOG_PAR_07 VALUES (7),
PARTITION COMMONUSERLOG_PAR_08 VALUES (8),
PARTITION COMMONUSERLOG_PAR_09 VALUES (9),
PARTITION COMMONUSERLOG_PAR_10 VALUES (10),
PARTITION COMMONUSERLOG_PAR_11 VALUES (11),
PARTITION COMMONUSERLOG_PAR_12 VALUES (12)
)
tablespace IISS_LOG_DAT;

comment on column T_IISS_LOG_COMMONUSERLOG.GID
is '日志ID（由单独的序列生成）';
comment on column T_IISS_LOG_COMMONUSERLOG.LOGDATE
is '上线时间';
comment on column T_IISS_LOG_COMMONUSERLOG.OFFDATE
is '下线时间';
comment on column T_IISS_LOG_COMMONUSERLOG.USERID
is '用户ID';
comment on column T_IISS_LOG_COMMONUSERLOG.USERTOKEN
is 'token';
comment on column T_IISS_LOG_COMMONUSERLOG.CLIENTCODE
is '终端软件编码';
comment on column T_IISS_LOG_COMMONUSERLOG.PORTALTYPE
is '访问类型0：web；1：wap；2：client';
comment on column T_IISS_LOG_COMMONUSERLOG.AREACODE
is '区域编码';
comment on column T_IISS_LOG_COMMONUSERLOG.IP
is '登录系统IP地址';
comment on column T_IISS_LOG_COMMONUSERLOG.LOGINIPADDR
is '登录系统IP地址（广东用）';
comment on column T_IISS_LOG_COMMONUSERLOG.CLIENTVERSION
is '客户端版本（广东用）';
comment on column T_IISS_LOG_COMMONUSERLOG.GWUA
is '终端型号，长UA字符串（广东用）';
comment on column T_IISS_LOG_COMMONUSERLOG.pageareacode
is '登录页面归属地市编码';
comment on column T_IISS_LOG_COMMONUSERLOG.LoginType
is '0 无线城市帐号（注册时管理模块返回的Userid）
1 网厅帐号（北京使用）
2 手机号码
3 用户自定义ID（昵称）
4 邮箱登录
默认是0';
comment on column T_IISS_LOG_COMMONUSERLOG.Gateway
is '01-CMWAP 02-CMNET 03-电信 04-联通 05-其他';
--start add bu fuqiang
comment on column T_IISS_LOG_COMMONUSERLOG.ENTERDATE
is '日志入库时间 默认是sysdate';
comment on column T_IISS_LOG_COMMONUSERLOG.PARTITION_MONTH
is 'oracle11G的虚拟列 不可以修改';
--end add bu fuqiang

create index IX_IISS_LOG_COMMONUSERLOG_DATE on T_IISS_LOG_COMMONUSERLOG (LOGDATE)
tablespace IISS_LOG_IDX;

create index IX_IISS_LOG_COMUSERLOG_LOGID on T_IISS_LOG_COMMONUSERLOG (USERID)
tablespace IISS_LOG_IDX;

create index I_IISS_LOG_COMMONUSERLOG_TOKEN on t_iiss_log_commonuserlog (USERTOKEN)
tablespace IISS_LOG_IDX;