Shulou(Shulou.com)06/01 Report--

In the previous article, I introduced and tested the new virtual column features introduced in the oracle 11g version, and this article continues to test virtual columns that are partitioned key-based tables:

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 (8pm 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))

14 PARTITION Q2q2005 VALUES LESS THAN (TO_DATE ('01MurJULMULMY')

15 PARTITION Q3 March 2005 VALUES LESS THAN (TO_DATE ('01 color Oct May 2005))

16 PARTITION Q40002005 VALUES LESS THAN (TO_DATE ('01MJANMUR 2006))

17)

(PARTITION Q1 2005 VALUES LESS THAN (TO_DATE ('01MurAPRMI 2005'))

*

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 (8pm 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))

16 PARTITION Q2q2005 VALUES LESS THAN (TO_DATE ('01MurJULMULMY')

17 PARTITION Q3 2005 VALUES LESS THAN (TO_DATE)

18 PARTITION Q40002005 VALUES LESS THAN (TO_DATE ('01MJANMUR 2006))

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 Q1mm 2005 TIMESTAMP' 2005-04-01 00VOV 0000'

ORDERS_VCOL Q2mm 2005 TIMESTAMP' 2005-07-01 00VlV 0000'

ORDERS_VCOL Q3 ORDERS_VCOL 2005 TIMESTAMP' 2005-10-01 00VOV 0000'

ORDERS_VCOL Q40002005 TIMESTAMP' 2006-01-01 00VOV 0000'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.