Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Virtual Columns in Oracle 11g R1

I just found out this good feature in Oracle 11g release 1. How many times you felt tired because you were coding again and again for a particular function while selecting data from table?

Consider the following table. The table contains employee name, date of joining. Year of joining will not be stored in database as it can be always derived from one data. An dependable data! Assume, you need it desperately for some sake. One way is use views to achieve the purpose.

With Oracle 11gR1 virtual columns, you can add an virtual column in table for easy selection.

SQL> create table empTable
 2         ( name varchar2(20)
 3         , doj date
 4         , yoj generated always as (to_char(doj,'yyyy')) virtual
 5         )
 6 /
Table created.

Virtual columns do not hold memory as it is always generated at the time of queried.
You mention ‘YOJ’ column is always generated. You can then use ‘DOJ’ to derive the data. ‘VIRTUAL’ keyword is optional, but it provides lot of readability. Let’s describe what we have created.

SQL> desc emptable
Name                         Null?    Type
---------------------------- -------- ---------------
NAME                                  VARCHAR2(20)
DOJ                                   DATE
YOJ                                   VARCHAR2(4)

Let try inserting some data into the table.

SQL> insert into emptable
 2              (name, doj)
 3     values
 4              ('Sam', sysdate)
 5 /
1 row created.

SQL> select *
 2      from emptable
 3 /
NAME                 DOJ       YOJ
-------------------- --------- ----
Sam                  05-NOV-09 2009

Restrictions on Virtual Column

Try inserting into virtual column?

SQL> insert into emptable
 2           (name, doj, yoj)
 3    values
 4           ('Ram', sysdate, 2009)
 5 /
insert into emptable
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns.

You might be thinking, why could not created a virtual column on the most important calculation? ‘AGE’ as it turns out, changes everyday. You grow older by day. If you try to calculate when you create the table, you will get the following error.

 5 , age generated always as
   (trunc(months_between(sysdate, dob)/12)) virtual
 6  )
SQL> /
, age generated always as
(trunc(months_between(sysdate, dob)/12)) virtual
                        *
ERROR at line 5:
ORA-54002: only pure functions can be specified in a virtual column expression

This is because, you need to have a deterministic function. A function which returns same value irrespective of when you call, how many time you call. trunc(doj, ‘yyyy’) is deterministic. Where as a function which depends on SYSDATE can not be and hence not allowed in virtual column. A detailed restriction is available here.

Indexes on Virtual Columns

This mean, we can also create index on virtual columns.

SQL> create index emptbl_yoj_ix1 on emptable(yoj)
  2  /
Index created.

Now, what about the nature of the index? It will be function based index. See below.

SQL> select index_name
 2        , index_type
 3    from dba_indexes
 4   where table_name = 'EMPTABLE'
 5 /
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
EMPTBL_YOJ_IX1                 FUNCTION-BASED NORMAL

Virtual columns do not occupy memory as they are always generated while queried.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: