导航：首页 > 数据库 >

How To Use Virtual Column-Based Partitioning

发表于：2024-12-13 作者：千家信息网编辑

千家信息网最后更新 2024年12月13日，上一篇我介绍和测试了oracle 11g版本引入的虚拟列新特性，本文继续测试分区键基于表的虚拟列：Prior releases of Oracle only allowed a table to be

千家信息网最后更新 2024年12月13日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'

很赞哦！