|
Oracle 11g: New Features - Partitioning on Virtual Columns
I will assume that you know about partitioning in general. If not, you
can read the following link:
Partitioning
On Oracle 11g, there have been lots of enhancements such as reference
partition, interval partitioning, partitioning virtual columns, and
extended composite partitioning.
Partitioning on Virtual columns
This feature allows you to create a column that is not stored in your
table. The column will be computed at run time. This column can be
partition on Oracle 11g database.
The following is an example of how to create a table partitioning on
virtual columns.
CREATE TABLE company-sales
(
SALES_ID NUMBER,
CUST_ID NUMBER,
SALES_AMT NUMBER,
SALE_CATEGORY VARCHAR2(6)
GENERATED ALWAYS AS
(
CASE
WHEN SALES_AMT <= 10000
THEN 'LOW'
WHEN SALES_AMT > 10000
AND SALES_AMT <= 100000
THEN CASE
WHEN CUST_ID < 101 THEN 'LOW'
WHEN CUST_ID BETWEEN 101 AND 200 THEN 'MEDIUM'
ELSE 'MEDIUM'
END
WHEN SALES_AMT > 100000
AND SALES_AMT <= 1000000
THEN CASE
WHEN CUST_ID < 101 THEN 'MEDIUM'
WHEN CUST_ID BETWEEN 101 AND 200 THEN 'HIGH'
ELSE 'ULTRA'
END
ELSE 'ULTRA'
END
)
VIRTUAL
)
PARTITION BY LIST (SALE_CATEGORY)
(
PARTITION P_LOW VALUES ('LOW'),
PARTITION P_MEDIUM VALUES ('MEDIUM'),
PARTITION P_HIGH VALUES ('HIGH'),
PARTITION P_ULTRA VALUES ('ULTRA')
)
Good Luck!
|