Virtual Columns on 11G

Using Virtual Columns on 11g:

• Indexes defined against virtual columns are equivalent to function-based indexes.
• Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.
• Tables containing virtual columns can still be eligible for result caching.
• Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
  
  • Constraint on the virtual column must be disabled and re-enabled.
  • Indexes on the virtual column must be rebuilt.
  • Materialized views that access the virtual column must be fully refreshed.
  • The result cache must be flushed if cached queries have accessed the virtual column.
  • Table statistics must be regathered.
• Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
• The expression used in the virtual column definition has the following restrictions:
  
  • It cannot refer to another virtual column by name.
  • It can only refer to columns defined in the same table.
  • If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
  • The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.

SQL> create table cv_test

(year number(4),

month number(2));

begin

for i in 1..12

loop

insert into cv_test(year,month) values(2010,i);

end loop;

end;

/

commit;

SQL> select * from cv_test;

YEAR MONTH

----- -----

2010 1

2010 2

2010 3

2010 4

2010 5

2010 6

2010 7

2010 8

2010 9

2010 10

2010 11

2010 12

12 rows selected

-- Create Virual Column

ALTER TABLE cv_test ADD year_month GENERATED ALWAYS AS ((year||lpad(month,2,'0')));

SQL> select * from cv_test;

YEAR MONTH YEAR_MONTH

----- ----- ------------------------------------------------

2010 1 201001

2010 2 201002

2010 3 201003

2010 4 201004

2010 5 201005

2010 6 201006

2010 7 201007

2010 8 201008

2010 9 201009

2010 10 201010

2010 11 201011

2010 12 201012

12 rows selected

SQL> create index ix_year_month on cv_test (year_month);

explain plan for

Select * from cv_test

where(year||LPAD(month, 2, '0')) = '201001';

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1997217315

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 78 | 2 (0

| 1 | TABLE ACCESS BY INDEX ROWID| CV_TEST | 1 | 78 | 2 (0

|* 2 | INDEX RANGE SCAN | IX_YEAR_MONTH | 1 | | 1 (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access(TO_CHAR("YEAR")||LPAD(TO_CHAR("MONTH"),2,'0')='201001')

Note

-----

- dynamic sampling used for this statement

18 rows selected

SQL> explain plan for

select * from cv_test

where year_month = '201001';

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1997217315

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 52 | 2 (0

| 1 | TABLE ACCESS BY INDEX ROWID| CV_TEST | 1 | 52 | 2 (0

|* 2 | INDEX RANGE SCAN | IX_YEAR_MONTH | 1 | | 1 (0

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("YEAR_MONTH"='201001')

Note

-----

- dynamic sampling used for this statement

18 rows selected