How To Use Virtual Column-Based Partitioning

      上一篇我介绍和测试了oracle 11g版本引入的虚拟列新特性，本文继续测试分区键基于表的虚拟列：
Prior releases of Oracle only allowed a table to be partitioned based on a physical column. Oracle 11g, with the addition of virtual columns, now allows a partition key based on an expression, using one or more existing columns of the table. A virtual column can now be used as a partitioning key.Oracle 11g supports the concept of virtual columns on tables. These virtual columns are not physically stored in the table, but derived from data in the table. These virtual columns can be used in the partition key in all basic partitioning schemes. The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.

SQL> CREATE TABLE orders  --------------------scripts from oracle 11g doc(Example 4-9 Creating reference-partitioned tables)
  2      ( order_id           NUMBER(12),
  3        order_date         TIMESTAMP WITH LOCAL TIME ZONE,
  4        order_mode         VARCHAR2(8),
  5        customer_id        NUMBER(6),
  6        order_status       NUMBER(2),
  7        order_total        NUMBER(8,2),
  8        sales_rep_id       NUMBER(6),
  9        promotion_id       NUMBER(6),
 10        CONSTRAINT orders_pk PRIMARY KEY(order_id)
 11      )
 12    PARTITION BY RANGE(order_date)
 13      ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
 14        PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
 15        PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
 16        PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
 17      );
    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
                                          *
ERROR at line 13:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals

SQL> CREATE TABLE orders_vcol
  2      ( order_id           NUMBER(12),
  3        order_date         TIMESTAMP(6) WITH LOCAL TIME ZONE,
  4        order_mode         VARCHAR2(8),
  5        customer_id        NUMBER(6),
  6        order_status       NUMBER(2),
  7        order_total        NUMBER(8,2),
  8        sales_rep_id       NUMBER(6),
  9        promotion_id       NUMBER(6),
 10        vcol_gmt TIMESTAMP(6) AS (SYS_EXTRACT_UTC(order_date))
 11        virtual,
 12        CONSTRAINT orders_vpk PRIMARY KEY(order_id)
 13      )
 14    PARTITION BY RANGE(vcol_gmt)
 15      ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
 16        PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
 17        PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
 18        PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
 19      );

Table created.
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A40
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name='ORDERS_VCOL'
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
ORDERS_VCOL               Q1_2005              TIMESTAMP' 2005-04-01 00:00:00'
ORDERS_VCOL               Q2_2005              TIMESTAMP' 2005-07-01 00:00:00'
ORDERS_VCOL               Q3_2005              TIMESTAMP' 2005-10-01 00:00:00'
ORDERS_VCOL               Q4_2005              TIMESTAMP' 2006-01-01 00:00:00'