上一篇我介紹和測試了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;
