bye bye to FBI; Lets Welcome VC

(FBI ==> Function Based Indexes ; VC ==> Virtual Columns )

starting 11g, you can have Virtual columns (expression or computations) in a table just like regular columns .. That would make lots of things readable..queries, index scripts etc etc..

you can have constraints based on Virtual columns..you can even partition the table based on VC.

Here is a simple demo
----------------------

Create table test_vc (
id number(5) ,
name varchar2(10) ,
age number(3) ,
sal number(10) ,
comm number(10) ,
grade varchar2(10) GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL ,
grade_auto GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL
)

Both Grade and Grade_auto are Virtual columns ..but the only difference is for Grade_auto Oracle assigned the datatype by its own judgement.

SQL> desc test_vc
Name Null? Type
----------------------------------------- -------- --------------
ID NUMBER(5)
NAME VARCHAR2(10)
AGE NUMBER(3)
SAL NUMBER(10)
COMM NUMBER(10)
GRADE VARCHAR2(10)
GRADE_AUTO VARCHAR2(8) <---

Before 11g, we either would have created an Index on user-defined deterministic function or standard built-in functions.. this would have been the Create Index statement

SQL> Create index test_f_idx on test_vc(
2 CASE WHEN (age>60)
3 THEN 'Senior'
4 WHEN ((age between 51 and 60) and (sal>=200000))
5 THEN 'Grade A+'
6 WHEN ((age between 51 and 60) and (sal<200000))
7 THEN 'Grade A-'
8 WHEN ((age between 41 and 50) and (sal>=200000))
9 THEN 'Grade B+'
10 WHEN ((age between 41 and 50) and (sal<200000))
11 THEN 'Grade B-'
12 WHEN ((age between 31 and 40) and (sal>=200000))
13 THEN 'Grade C+'
14 WHEN ((age between 31 and 40) and (sal<200000))
15 THEN 'Grade C-'
16 END
17 );

Now check out how the same reads..

-- in 11g (indexing on Virtual columns allowed)
-- ---------------------------------------------------------
Create index test_vc_idx1 on test_vc(grade);
Create index test_vc_idx2 on test_vc(grade_auto);

Behind the screens, Oracle handles the Virtual columns just the same way as FBIs

SQL> select index_name,index_type from user_indexes
2 where table_name='TEST_VC';

INDEX_NAME INDEX_TYPE
------------------------------ ------------------------
TEST_VC_IDX2 FUNCTION-BASED NORMAL
TEST_VC_IDX1 FUNCTION-BASED NORMAL
TEST_F_IDX FUNCTION-BASED NORMAL

so lets forget Function based Index and start using Virtual columns..