11g New Features - Virtual column

The other day while going through Oracle forums, I found an interesting question on partitioning. The gentlemen had a problem which most of us face; He wanted to partition a table based on year and month but both columns were being stored in CHAR datatype; here is the table structure

CREATE TABLE SALES
(
YEARS CHAR(4 BYTE),
MONTH CHAR(2 BYTE),
)

The solution, I gave, was to add a new column just for the partition purpose. In fact thats how it used to happen till 10g. But 11g comes with an interesting feature; you can now create a virtual column. I have used the same table structure to explain how virtual column works in 11g. Basically virtual column is nothing but a derived column; you do not insert data into it rather it derives its value based on the values inputed in some other columns or combination of columns. It even gives you the capability to write a small piece of code to generate the value.

Create the table with a virtual column to partition the data into 4 partitions Q1, Q2, Q3 and Q4.

CREATE TABLE SALES
(
YEARS VARCHAR2(4),
MONTH VARCHAR2(2),
PART_COL
VARCHAR2(6)
generated always as
(
case
when MONTH in ('01','02','03')
then 'Q1'
when MONTH in ('04','05','06')
then 'Q2'
when MONTH in ('07','08','09')
then 'Q3'
when MONTH in ('10','11','12')
then 'Q4'
end
) virtual
)
partition by list (PART_COL)
(
partition p_q1 values ('Q1'),
partition p_q2 values ('Q2'),
partition p_q3 values ('Q3'),
partition p_q4 values ('Q4')
);

Insert data into the table

insert into sales (years,month) values ('2007','01');
insert into sales (years,month) values ('2007','01');
insert into sales (years,month) values ('2007','02');
insert into sales (years,month) values ('2007','02');
insert into sales (years,month) values ('2007','12');
insert into sales (years,month) values ('2007','09');
insert into sales (years,month) values ('2007','05');
insert into sales (years,month) values ('2007','07');
insert into sales (years,month) values ('2007','11');

SIPRAS@11glab> commit;

Now select from the table and see how the data has gone into different partitions

SIPRAS@11glab> select * from sales;

YEAR MO PA
---- -- --
2007 01 Q1
2007 02 Q1
2007 02 Q1
2007 01 Q1
2007 05 Q2
2007 09 Q3
2007 07 Q3
2007 12 Q4
2007 11 Q4

9 rows selected.

SIPRAS@11glab> select * from sales partition (p_q1);

YEAR MO PA
---- -- --
2007 01 Q1
2007 02 Q1
2007 02 Q1
2007 01 Q1

SIPRAS@11glab> select * from sales partition (p_q2);

YEAR MO PA
---- -- --
2007 05 Q2

SIPRAS@11glab> select * from sales partition (p_q3);

YEAR MO PA
---- -- --
2007 09 Q3
2007 07 Q3

SIPRAS@11glab> select * from sales partition (p_q4);

YEAR MO PA
---- -- --
2007 12 Q4
2007 11 Q4

According to me, Virtual column is a real powerful addition. In situation like this I need not force the development team to insert data into the new column that will be used for partition or write a trigger for it. Simply creating virtual column and partitioning it would do wonders. Moreover it's use not limited only for partitions; it can be used in general e.g. wherever we use a derived columns which gets it value through a trigger or a stored procedures or a default value (only to be updated by a piece of code later).

Reference :- Oracle Database VLDB and Partitioning Guide 11g Release 1 (11.1)