As the name suggests, Oracle introduced the concept of defining columns on a table that will actually not store values. Instead, it is uses an expression based on other columns in the same table. A very interesting concept.

Although these virtual columns appear as normal columns of the table when queried, they are actually not physically stored in the table. Instead, derived from data in the other columns of the table using expressions or functions.

The syntax for defining a virtual column is listed below:

”column_name
[GENERATED ALWAYS] AS (expression) [VIRTUAL]”]

If the datatype is omitted while creating a virtual column, it is determined based on the result of the expression used. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

Here is a good example of using virtual columns from a recent project where I needed to create bunch of percentages based on data stored and I leveraged the power of virtual columns. I would have left these percentage calculations to the reporting tool but I decided to let Oracle do the job for me and here is the table I created:

CREATE TABLE DAILY_SUPPORT_SUMMARY(
   TICKET_DATE   date,
   TOTAL_TICKETS_OPENED   number(9),
   TOTAL_CLOSED   number(9),
   TOTAL_WIP   number(9),
   PERCENTAGE_PROCESSED   number(9,2) AS
      (
            ((TOTAL_CLOSED+ TOTAL_WIP)/ TOTAL_TICKETS_OPENED)*100
      ) VIRTUAL,
   PERCENTAGE_CLOSED   number(9,2) GENERATED ALWAYS AS
      (
            (TOTAL_CLOSED/ TOTAL_TICKETS_OPENED)*100
      ) ,
   PERCENTAGE_WIP   number(9,2) AS
      (
            (TOTAL_WIP/ TOTAL_TICKETS_OPENED)*100
      )
);

Please note I have not used “GENERATED ALWAYS” and VIRTUAL keywords in all virtual column definition. This is for demonstration purposes of this post. You can choose to use them or not depending on your style.

Although data in my table was inserted using ODI data integration tool by Oracle, here are some example INSERT statements to insert data in this table to see how we can insert data in this table:

INSERT INTO DAILY_SUPPORT_SUMMARY (TICKET_DATE, TOTAL_TICKETS_OPENED, TOTAL_CLOSED, TOTAL_WIP) VALUES (‘01-May-15’, 150, 100, 50);

INSERT INTO DAILY_SUPPORT_SUMMARY (TICKET_DATE, TOTAL_TICKETS_OPENED, TOTAL_CLOSED, TOTAL_WIP) VALUES (‘02-May-15’, 250, 250, 0);

INSERT INTO DAILY_SUPPORT_SUMMARY (TICKET_DATE, TOTAL_TICKETS_OPENED, TOTAL_CLOSED, TOTAL_WIP) VALUES (‘01-May-15’, 90, 50, 40);

COMMIT;

Please note that we only insert data in the fields that are not virtual columns in this table. If you try to insert data in the virtual fields, you will be faced with an error message that will look like this:

ORA-54013: INSERT operation disallowed on virtual columns

Features of Virtual Columns in Oracle

Virtual columns are useful as I showed in my example above and here are the list of things that you can do around virtual columns:

  • You can define Indexes against virtual columns as well. However, please note that indexes defined against virtual columns are equivalent to function-based indexes.
  • Virtual columns can be referenced in the column list of SELECT statements and also in the WHERE clause of SELECT, UPDATE and DELETE statements.
  • Tables containing virtual columns can still be eligible for result caching.
  • Virtual columns can be used in the partition key in all basic partitioning schemes.

 

Limitations of Virtual Columns in Oracle

  • DML operations on the virtual columns are not allowed.
  • While virtual columns are candidate for indexing, virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
  • The expressions used in creating virtual columns as following limitations:
    • It cannot refer to any other column of another table
    • It cannot refer to another virtual column with its name although the same expression can be used.
    • The column expression for virtual columns can refer to a PL/SQL function if the function is designated DETERMINISTIC during its creation.

Conclusion

Virtual columns in Oracle database is a feature that can make some tasks such as partitioning on date fields very easy. If you didn’t know how to use it, I hope you found my example helpful in trying this awesome feature of Oracle 11g and beyond.

Time to Share Your Knowledge and Experience

Have you used virtual columns in any of your Oracle projects so far? If so, please share your knowledge about it and help this community with your experience.

If you learned about virtual columns through this post, tell us what you think of this feature? Do you know any other databases that have this feature or better?

If you have any questions about this topic, kindly write about that as well. We love to hear from you and answer your questions.