Virtual Columns Indexes on VC and Partitioning on VC

From Oracle FAQ
Jump to: navigation, search
Road Works This article may require cleanup to meet OraFAQ's quality standards.
Please improve this article if you can.
Road Works



[edit] Virtual Columns

The value of the virtual column is a derived expression.
–Can be derived from columns of the same table
–Can be derived from constants
–Can include SQL or user-defined PL/SQL functions
•You can create an index or partition on a virtual column
•Index Organized and External Tables can NOT have virtual columns
•You can NOT explicitly write to a virtual column

SQL> create table test
 2  ( first_name varchar2(30),
 3  salary number(6),
 4  anual_sal  generated always as (salary*12));
Table created.

Let see the describe for the table we just created with virtual column.

SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- ------------
FIRST_NAME                                         VARCHAR2(30)
SALARY                                             NUMBER(6)
ANUAL_SAL                                          NUMBER

I can't see any additional entry for virtual column.

SQL> insert into test
 2  values
 3  ('Michel',500);
insert into test            *
ERROR at line 1:
ORA-00947: not enough values

As you noticed that I specify 2 values but table have 3 columns so for that we should explicitly mention the column name because we are not suppose to insert into virtual column.

SQL> insert into test
 2  (first_name,salary)
 3  values
 4  ('Michel',500);
1 row created.

SQL> insert into test
 2  (first_name,salary)
 3  values
 4  ('Ora',1500);
1 row created.

SQL> insert into test
 2  (first_name,salary)
 3  select first_name,salary from employees
 4  where rownum<5;
4 rows created.

SQL> commit;
Commit complete.

SQL> select * from test;

FIRST_NAME SALARY     ANUAL_SAL
---------- ---------- ----------
Michel            500       6000
Ora              1500      18000
Donald           2600      31200
Douglas          2600      31200
Jennifer         4400      52800
Michael         13000     156000

6 rows selected.
                                        

[edit] Indexes On Virtual Columns

As Oracle says we can create indexes on virtual columns let try to create one.

SQL> create index my_ind on test(anual_sal);
Index created.

Let check whether oracle will use this index or not.

SQL> explain plan for
 2  select * from test
 3  where anual_sal=31200;
Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    43 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     1 |    43 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MY_IND |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Oh yea Oracle works beautiful with indexes on virtual columns.The MY_IND is mine index name which I created on virtual column ANNUAL_SAL.

Now I’m going to check some other attributes for the virtual column.

SQL> alter table test drop column salary;
alter table test drop column salary *
ERROR at line 1:
ORA-54031: column to be dropped is used in a virtual column expression

Awww I’m unable to drop the column salary because ANNUAL_SAL the virtual column based on this let 1st try to drop virtual column.

SQL> alter table test drop column anual_sal;
Table altered.
SQL> alter table test drop column salary;
Table altered.

Now I’m again going to add salary column and then virtual column on the base of salary column.

SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- ------------
FIRST_NAME                                         VARCHAR2(30)

SQL> alter table test add (salary number(6));
Table altered.

SQL> alter table test add (anual_sal as (salary*12));
Table altered.

[edit] Partitions on Virtual Columns

SQL> drop table test ;
Table dropped.

SQL> purge tablespace users;
Tablespace purged.

SQL> create table test
 2      (first_name varchar2(30),
 3      salary number (6),
 4      anual_sal generated always as (salary*12))
 5      partition by range (anual_sal)
 6      (
 7      partition p1 values less than (15000))
 8  tablespace users;
Table created.

Let check it in USER_TAB_PARTITIONS

SQL> select table_name,partition_name,tablespace_name
 2  from user_tab_partitions
 3* where table_name='TEST';
TABLE_NAME  PARTITION_NAME TABLESPACE_NAME
----------- -------------- ---------------
TEST        P1             USERS

Let add another partition to existing table.

SQL> alter table test add partition p2
 2  values less than (maxvalue);
Table altered.
SQL> select table_name,partition_name,tablespace_name
 2  from user_tab_partitions
 3  where table_name='TEST';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
---------- -------------- ---------------
TEST       P1             USERS
TEST  	    P2             USERS

Let check the number of rows in each partition.

SQL> select table_name,partition_name,tablespace_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST';
TABLE_NAME PAR	TABLESPACE_NAME NUM_ROWS
---------- --- ---------------	--------
TEST 	    P1  USERS                  0
TEST 	    P2  USERS                  0

Oh I should collect statistics on table TEST.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant execute on dbms_stats to hr;
Grant succeeded.

SQL> show user
USER is "HR"
SQL> exec dbms_stats.gather_table_stats('HR','TEST');
PL/SQL procedure successfully completed.

SQL> select table_name,partition_name,tablespace_name,num_rows
 2  from user_tab_partitions
 3  where table_name='TEST';
TABLE_NAME PAR	TABLESPACE_NAME NUM_ROWS
---------- --- ---------------	--------
TEST 	    P1  USERS                  1
TEST 	    P2  USERS                107

Hope this topic would help you to create virtual columns indexes on virtual columns and partitioning on virtual columns.