Partitioning has always been one of the most useful tools but with Oracle Database 11g. In version 11, there is a new enhancement:
Partitioning on Virtual Columns
Suppose in your table called sales, you have the following columns:
SQL> desc sales
Name Null Type
—————- ——– ——
SALES_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
And you want to partition this table by some scheme that allows you to purge and archive is based on the amount of sale. Here are the four categories of sale:
If sale_amt is
|and cust_id is||Then sale_category is|
You want to partition this table on the column sale_category, but there is a problem: there is no column called sale_category. This is something you derived from the sale_amt column. How can you partition this table, then?
In earlier versions of Oracle you could have put a new column in the table called sale_category, and used a trigger to populate the column using the logic shown in the table. But the presence of the new column would have caused another performance hit due to the triggers.
In Oracle Database 11g, a new feature called Virtual Columns allows you to create a column that is not stored in the table but rather is computed at run time. You can also partition on this column. Using this feature, it’s a breeze to partition this table.
create table sales
generated always as
when sales_amt <= 50000
when sales_amt > 50000
and sales_amt <= 500000
when cust_id < 101 then ‘LOW’
when cust_id between 101 and 200 then ‘MEDIUM’
when sales_amt > 500000
and sales_amt <= 5000000
when cust_id < 101 then ‘MEDIUM’
when cust_id between 101 and 200 then ‘HIGH’
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’)
Now if you insert rows:
SQL> insert into sales (sales_id,cust_id,sales_amt) values (1,1,100); 1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (2,1,1500); 1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (3,102,1500); 1 row created.
SQL> insert into sales (sales_id,cust_id,sales_amt) values (4,102,10000); 1 row created.
SQL> commit; Commit complete.
The record was placed in the appropriate partition.
Partitioning on virtual columns allows you to create partitions that make sense for business even though the column itself is not present. Here you have used a very simple calculation for the virtual column but it can be as complex as you like. In those cases, partitioning on a virtual column becomes even more valuable.