Virtual columns in Oracle. Use and Limitations

Hello everyone, today we wanted to refresh with you an Oracle utility that although it is very powerful we usually use it little, the virtual columns. Virtual columns are columns of Oracle tables whose contents are based on a formula or expression that makes use of data from other columns. They should be understood almost as one more column of the table, being able to be used as part of the indexing of the table, as foreign keys in another table, include comments, etc. But EYE, they are READ-ONLY, you can not dump information into them through an INSERT or UPDATE statement.

These columns “penalize” the CPU, since they are calculated “on the fly” (in insert, update, deletes, select and others, such as enable and disable constraints), but they do not consume storage space. Also consume memory, because if we make a fetch, the values are saved in the memory as with the rest of the fields.

The formula or expression that can be used may include PL/SQL functions, but you run the risk of penalizing performance, so that it is recommended to leave as simple as possible so as not to have this problem.

Limitations of Virtual Columns in Oracle

A number of limitations will have to be taken into account. In these columns you cannot:

  • “Nest” virtual columns, that is, we cannot use a virtual column in the expression or formula of another virtual column.
  • Use columns from other tables for the expression (actually if, if done within a PL/SQL function), only those in the table to which the virtual column belongs.
  • Use certain column types (LONG or user-defined fields for example) for virtual columns.

The syntax would be as follows:

Syntax Virtual columns in Oracle. GPSOS

Our virtual column in this case is col4 and contains the difference in days between two dates. If we insert records in our new table we see:

SQL> insert into TEST (col1, col2, col3) values (1, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'));
  
 1 row created.
  
 SQL> select * from TEST;
  
 COL1 COL2 COL3 COL4
  ---------- --------- --------- ----------
  1 20-FEB-21 22-FEB-21 2  

In col4 it already calculates directly the difference in days of the two dates, 2 in this case. Although we can use the column like any other in the database, write operations on it are not allowed. If we try for example to insert the value:

insert into TEST (col1, col2, col3, col4) values (2, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'), 2); 
ERROR at line 1: 
 ORA-54013: INSERT operation disallowed on virtual columns 

Nor can we insert as if the table had only three fields since it will fail us:

insert into TEST values (1, to_date('20/02/2021','DD/MM/YYYY'), to_date('22/02/2021','DD/MM/YYYY'));
  
 ERROR at line 1:
 ORA-00947: not enough values 

To verify that the value is automatically updated we tried to change one of the dates:

 SQL> update TEST set col3 = to_date('25/02/2021','DD/MM/YYYY') where col1=1;
 1 row updated.
 SQL> select * from TEST;
 COL1 COL2 COL3 COL4
  ---------- --------- --------- ----------
 1 20-FEB-21 25-FEB-21 5 

The value changes from 2 to 5 because it is recalculated in the select.

Using indexes in these columns

An important utility of the functionality is to be able to create indexes on the virtual columns, this is where we can obtain important performance advantages. In this case the virtual column does take up space, in this case in the index:

create index PRUEBA_I1 on TEST (col4);

If we look at the new execution plan when we use the field col4:

 SQL> explain plan for select * from TEST where col4 SELECT * FROM table(DBMS_XPLAN. DISPLAY);
 Hash value plan: 38723808
  
  -----------------------------------------------------------------------------------------
 | id | Operation | Name | Rows | bytes | Cost (%CPU)| Time |
  -----------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 1 | 44 | 0 (0)| 00:00:01 |
 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 44 | 0 (0)| 00:00:01 |
 |* 2 | INDEX RANGE SCAN | PRUEBA_I1 | 1 | | 0 (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
  ---------------------------------------------------
  
 2 - access("COL4"<5)
  
 Note
  -----
 - dynamic sampling used for this statement (level=2)
  
 18 rows selected. 

We see that you use it and this can make the performance improve markedly by being able to avoid the calculation and search of the one-to-one records.

We can also see the values of our columns virtual with the following query:

 SELECT 
 column_name, 
 virtual_column,
 data_default
 FROM 
 all_tab_cols
 WHERE table_name = 'PROOF';
  
  
 COLUMN_NAME VIR DATA_DEFAULT
  ------------------------------ --- ------------
 COL1 NO
 COL2 NO
 COL3 NO
 COL4 YES "COL3"-"COL2" 

We hope that the entry is of your interest, see you in the next post.

Greetings.

Not to miss the following post or any of our publications. Subscribe to our newsletter. With only one email per month you will be informed of our publications.

More information: https://oracle-base.com/articles/11g/virtual-columns-11gr1

Comments are closed.