Virtual Columns No Longer Have to Be Virtual: Materialized Expression Columns in Oracle 23ai

Virtual columns were introduced in Oracle 11g to conveniently include computed logic directly in a table, appearing just like any other column. The expression used must be deterministic, meaning it must always produce the same result given the same inputs. This expression can use literals and/or scalar values from other columns in the same row and can call functions that meet the deterministic requirement.

They allow business logic to be integrated directly into the data at the table level, becoming part of the table's DDL. Previously, this would have been implemented via a user-defined view or within the logic of a trigger materializing the computed result into a new column on the table.

Using views would require the consumers of the data to be aware of, and have access to, the view in question. Often, the same logic would be duplicated across multiple views and in code as developers were unaware of their existence. Triggers, on the other hand, conceal logic in code and can be disabled leaving the column empty whereas virtual columns never fail to be populated with a valid result.

CREATE TABLE orders (
  id NUMBER,
  product VARCHAR2(255),
  price NUMBER(10,2),
  price_with_tax NUMBER(10,2) GENERATED ALWAYS AS (ROUND(price * 1.2, 2)) VIRTUAL
);

In the syntax above, the "GENERATED ALWAYS" and "VIRTUAL" keywords indicate that this calculation is done on-the-fly whenever the column is queried, and the result is not stored in the table. The trade-off here is potentially slower query performance in exchange for using less storage. You can create an index on the virtual column, which acts as a function-based index. This can significantly improve queries where the virtual column is used in the WHERE clause or ORDER BY. However, if the column is in the SELECT statement, there will still be some overhead for each row returned as it derives the expression’s result.

New in Oracle 23ai is a small enhancement that combines the benefits of traditional virtual columns and other pre-computed solutions: Materialized Expression Columns. In simple terms, these are virtual columns where the result of the expression is stored at the time of insert or update. This greatly reduces the overhead of calculating the column’s result during queries on the table. The business logic remains defined at the table level, and no custom triggers are needed.

CREATE TABLE orders (
  id NUMBER,
  product VARCHAR2(255),
  price NUMBER(10,2),
  price_with_tax NUMBER(10,2) GENERATED ALWAYS AS (ROUND(price * 1.2, 2)) VIRTUAL MATERIALIZED 
    -- Now materialized with this additional keyword
);

The downsides of using this are the overhead during DML operations, similar to when a trigger-based solution is used, and the additional storage requirements. However, you can use the column like any other. For example, you can add a custom index to it, partition the data by it, have Oracle generate histograms for the optimizer, and Exadata can use them for storage indexes.

In conclusion, virtual columns have evolved for the first time since their introduction in Oracle 11g with the new Materialized Expression Columns in Oracle 23ai. They provide a convenient way to encapsulate business logic at the DDL layer and now offer the option to have the computed results stored automatically.