Oracle 23ai (23.7) — Materialized Expression Columns

Introduced in Oracle Database version 11.1, Virtual Columns allow users to compute column values without physically storing them in the database. These columns are defined using expressions that can include constants, SQL functions, columns from the same table, and more. The values are calculated dynamically at runtime, meaning they occupy no storage space.

Example:

SQL> CREATE TABLE TBL_EMP_VIRTUAL_COL (
       id NUMBER, 
       PRE_TAX_SAL NUMBER, 
       NET_SAL NUMBER GENERATED ALWAYS AS (PRE_TAX_SAL * 0.10)
     );

Table created.

SQL> INSERT INTO TBL_EMP_VIRTUAL_COL(id, PRE_TAX_SAL) VALUES (1, 6523);

1 row inserted.

SQL> SELECT * FROM TBL_EMP_VIRTUAL_COL;

        ID PRE_TAX_SAL    NET_SAL
---------- ----------- ----------
         1        6523      652.3

In this example, the value for the NET_SAL column is calculated on-the-fly when queried.

Oracle Database 23ai (version 23.7) introduces Materialized Expression Columns, a new feature similar to Virtual Columns but with a key difference: the computed values are physically stored in the table. These values are calculated during DML operations such as INSERT, UPDATE, and DELETE, making data retrieval faster as it eliminates the need for recalculations.

Example:

SQL> CREATE TABLE TBL_EMP_MAT_COL (
       id NUMBER, 
       PRE_TAX_SAL NUMBER, 
       NET_SAL NUMBER AS (PRE_TAX_SAL * 0.10) MATERIALIZED
     );

Table created.

SQL> INSERT INTO TBL_EMP_MAT_COL(id, PRE_TAX_SAL) VALUES (1, 6523);

1 row inserted.

SQL> SELECT * FROM TBL_EMP_MAT_COL;

        ID PRE_TAX_SAL    NET_SAL
---------- ----------- ----------
         1        6523      652.3

The primary benefit of Materialized Expression Columns over Virtual Columns is their physical storage. This results in significantly faster data retrieval since values are precomputed and stored, eliminating the need for on-the-fly calculations during query execution.