Virtual columns in Oracle. Use and limitations

Hello everyone, today we wanted to refresh with you a utility of Oracle that although it is very powerful we usually use it little, virtual columns . Virtual columns are columns in Oracle tables whose content is based on a formula or expression that makes use of data from other columns. They should be understood almost as one more column in the table, and can be used as part of the indexing of the table, as foreign keys in another table, include comments, etc. But BEWARE, they are from READ ONLY , you cannot 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. Too consume memory, since if we do a fetch, the values are saved in the memory as with the rest of the fields.

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

Limitations of virtual columns in Oracle

A series 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 expression (Actually yes, if it is done inside a PL / SQL function), only those of 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 the following:

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 

On col4 It already directly calculates the difference in days of the two dates, 2 in this case. Although we can use the column like any other in the database, writing 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 

We also can’t 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 updated automatically we try 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 statement.

Using indexes on 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 TEST_I1 on TEST (col4);

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

 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 | TEST_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 performance significantly improve by being able to avoid calculating and searching for records one by one.

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

 SELECT 
 column_name, 
 virtual_column,
 data_default
 DESDE 
 all_tab_cols
 WHERE table_name = 'TEST';
  
  
 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 entry.

Greetings.

So as not to miss the next entry or any of our publications. Subscribe to our newsletter . With a single email a month you will be informed of our publications.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn

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