My recent blog post about Oracle Database 23.7 has generated significant interest in one particular feature: materialized columns. In this post, I’ll dive deeper into what materialized columns are, how they work, and when you might want to use—or avoid—them.
Table of Contents
- Background
- Creating materialized columns
- Can you change the value of a materialized column?
- Can you index a materialized column?
- Can you use a user-defined function?
- When should you use a materialized vs a virtual column?
- Conclusion
Background
Materialized columns, introduced in Oracle Database 23.7, are virtual columns whose values are persisted during DML operations. Unlike traditional virtual columns, which evaluate their expressions only when queried (e.g., in a SELECT statement), materialized columns shift the calculation from query time (SELECT) to DML time (INSERT or UPDATE). Virtual columns have long been useful for calculating values on the fly without storing them for values that can always be recomputed. For example, consider a table that stores both the net price and the tax rate. The total price can be calculated as net price * (1 + tax rate). While you could store the total price as well, this creates a direct dependency: if either the net price or tax rate changes, the total price must also be updated to avoid inconsistencies. However, since the net price and tax rate are always known, there’s no need to store the total price. Instead, a virtual column can be used, offering two benefits:
- It avoids storing an additional field, thereby saving disk space.
- It eliminates the need to manually maintain the dependency between net price, tax rate, and total price, ensuring the total price is always accurate when calculated.
However, virtual columns also have a downside: the calculation is performed every time the column is queried. If your workload frequently retrieves the total price, the repeated calculations can unnecessarily slow down SELECT statements. One solution is to store the total price in the table, accepting the additional storage overhead, to eliminate the calculation cost during retrieval. Yet, this approach reintroduces the maintenance overhead of managing dependencies between net price, tax rate, and total price. Wouldn’t it be ideal to calculate these values automatically, persist them on disk to avoid repeated computations, and still avoid the maintenance burden of manually updating dependent fields? Materialized columns address this by combining the benefits of calculated values with persistent storage, ensuring efficient retrieval without the need to manually manage dependencies.
Creating materialized columns
To create a materialized column in your table, you can use the new MATERIALIZED keyword:
Expand/Collapse
SQL> -- Drop the products table if it exists
SQL> DROP TABLE IF EXISTS products;
Table PRODUCTS dropped.
SQL> -- Create products table with a generated total_price "materialized" column
SQL> -- The total_price is calculated as net_price * (1 + tax_rate/100)
SQL> -- The total_price column is defined as MATERIALIZED to ensure it is always up-to-date with
SQL> CREATE TABLE products (
product_id NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
product_name VARCHAR2(100) NOT NULL,
net_price NUMBER(10,2) NOT NULL,
tax_rate NUMBER(5,2) NOT NULL,
total_price NUMBER(10,2)
GENERATED ALWAYS AS (net_price * (1 + tax_rate/100)) MATERIALIZED NOT NULL
);
Table PRODUCTS created.
Just with virtual columns, when you enter some rows in your table, the materialized column values will be generated:
Expand/Collapse
SQL> -- Insert sample data into products table
SQL> INSERT INTO products (product_name, net_price, tax_rate)
VALUES ('Laptop', 999.99, 8.25),
('Smartphone', 699.99, 8.25),
('Headphones', 149.99, 6.50),
('Tablet', 499.99, 8.25),
('Smartwatch', 249.99, 6.50);
5 rows inserted.
SQL> -- Commit the transaction to save the changes
SQL> COMMIT;
Commit complete.
SQL> -- Query to check the inserted data and the calculated total_price
SQL> SELECT product_name, net_price, total_price
FROM products;
PRODUCT_NAME NET_PRICE TOTAL_PRICE
_______________ ____________ ______________
Laptop 999.99 1082.49
Smartphone 699.99 757.74
Headphones 149.99 159.74
Tablet 499.99 541.24
Smartwatch 249.99 266.24
And as you would expect, when you change the value of the net price or the tax rate, the total price will adjust accordingly:
Expand/Collapse
SQL> -- Let's update the net_price and tax_rate for the Laptop product
SQL> UPDATE products
SET tax_rate = 10
WHERE product_name = 'Laptop';
1 row updated.
SQL> -- Commit the transaction to save the changes
SQL> COMMIT;
Commit complete.
SQL> -- Query to check the updated data and the recalculated total_price
SQL> SELECT product_name, net_price, total_price
FROM products;
PRODUCT_NAME NET_PRICE TOTAL_PRICE
_______________ ____________ ______________
Laptop 999.99 1099.99
Smartphone 699.99 757.74
Headphones 149.99 159.74
Tablet 499.99 541.24
Smartwatch 249.99 266.24
Can you change the value of a materialized column?
You might be wondering whether, since a materialized column stores its value on disk like a regular column, you can directly modify it and bypass the calculation. The answer is no. A materialized column functions the same as a virtual column in this regard. If you attempt to modify it directly, you will receive the same error that you get when you try to modify a virtual column:
Expand/Collapse
SQL> -- Let's try to change the total_price directly, which should not be allowed
SQL> UPDATE products
SET total_price = 1082.49
WHERE product_name = 'Laptop';
Error at Command Line : 2 Column : 8
Error report -
SQL Error: ORA-54017: UPDATE operation disallowed on virtual columns
https://docs.oracle.com/error-help/db/ora-54017/54017. 0000 - "UPDATE operation disallowed on virtual columns"
*Cause: Attempted to update values of a virtual column
*Action: Re-issue the statement without setting values for the
virtual column
Can you index a materialized column?
You may also wonder whether you can index a materialized column. Well, the answer is yes. Just like with virtual columns, you can also index materialized columns:
Expand/Collapse
SQL> -- Let's create an index on the total_price column for performance optimization
SQL> -- This will help speed up queries that filter or sort by total_price
SQL> -- Note: Although total_price column is materialized it can nevertheless be indexed
SQL> CREATE INDEX products_idx ON products (total_price);
Index PRODUCTS_IDX created.
SQL> set autotrace on explain;
Autotrace Enabled
Displays the execution plan only.
SQL> -- Query to find products with total_price greater than 450
SQL> -- This will utilize the index created on total_price for better performance
SQL> SELECT * FROM products WHERE total_price > 450;
PRODUCT_ID PRODUCT_NAME NET_PRICE TAX_RATE TOTAL_PRICE
_____________ _______________ ____________ ___________ ______________
4 Tablet 499.99 8.25 541.24
2 Smartphone 699.99 8.25 757.74
1 Laptop 999.99 10 1099.99
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
SQL_ID fmj3x8jsykcnt, child number 0
-------------------------------------
SELECT * from products where total_price > 450
Plan hash value: 3473403009
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 3 |
|* 2 | INDEX RANGE SCAN | PRODUCTS_IDX | 3 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TOTAL_PRICE">450)
SQL>
Can you use a user-defined function?
You might wonder whether, instead of embedding an expression directly in the column definition within your CREATE TABLE statement, you can use a user-defined function (UDF) that you’ve written yourself. The answer is yes, but with one caveat: the user-defined function must be marked as deterministic.
What does “deterministic” mean? A deterministic function always returns the same result for the same inputs. For example, if you have a function ADD() that adds two numbers, it will consistently produce the same result, such as 1 + 2 always equaling 3.
Why is this important? If your function is not deterministic and could produce different results for the same inputs, materializing the value at a given time would be unreliable. The next time the function is called with the same inputs, it might return a different result, rendering the stored value incorrect and making materialization pointless.
The database will not allow you to use a non-deterministic function as the source for your materialized column:
Expand/Collapse
SQL> -- Create a function to calculate total price based on net price and tax rate
SQL> -- Although the funcion is deterministic, it is not marked as such and hence it cannot
SQL> -- be used in a materialized column definition
SQL> CREATE OR REPLACE FUNCTION calculate_total_price(
p_net_price IN NUMBER,
p_tax_rate IN NUMBER
) RETURN NUMBER
AS
BEGIN
RETURN ROUND(p_net_price * (1 + p_tax_rate / 100), 2);
END;
/
Function CALCULATE_TOTAL_PRICE compiled
SQL> -- Change the total_price column to use the function for calculation
SQL> ALTER TABLE products DROP COLUMN total_price;
Table PRODUCTS altered.
SQL> ALTER TABLE products
ADD total_price NUMBER
GENERATED ALWAYS AS (calculate_total_price(net_price, tax_rate)) MATERIALIZED
NOT NULL;
Error starting at line : 3 in command -
ALTER TABLE products
ADD total_price NUMBER
GENERATED ALWAYS AS (calculate_total_price(net_price, tax_rate)) MATERIALIZED
NOT NULL
Error report -
ORA-30553: The function is not deterministic.
You cannot change the column expression for materialized or virtual columns, hence you need to recreate them. See Modifying an Existing Column Definition in the Oracle Database documentaiton for more information.
You will have to mark the function as DETERMINISTIC. Note, it is important that you not only mark the function as DETERMINISTIC but also ensure that it is deterministic:
Expand/Collapse
SQL> -- Change the function to be deterministic
SQL> -- This allows the function to be used in a materialized column definition
SQL> CREATE OR REPLACE FUNCTION calculate_total_price(
p_net_price IN NUMBER,
p_tax_rate IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN ROUND(p_net_price * (1 + p_tax_rate / 100), 2);
END;
/
Function CALCULATE_TOTAL_PRICE compiled
SQL> ALTER TABLE products
ADD total_price NUMBER
GENERATED ALWAYS AS (calculate_total_price(net_price, tax_rate)) MATERIALIZED
NOT NULL;
Table PRODUCTS altered.
SQL> -- Query to check the inserted data and the calculated total_price
SQL> SELECT product_name, net_price, total_price
FROM products;
PRODUCT_NAME NET_PRICE TOTAL_PRICE
_______________ ____________ ______________
Laptop 999.99 1099.99
Smartphone 699.99 757.74
Headphones 149.99 159.74
Tablet 499.99 541.24
Smartwatch 249.99 266.24
For more information about
DETERMINISTICfunctions, see the DETERMINISTIC Clause in the Oracle Database documentation
Should you use a user-defined function?
Should you use a user-defined function for your materialized column? That’s a loaded question, and whether you should use a user-defined function depends entirely on your use case. However, one important consideration for this scenario is that if you modify your user-defined function and it starts returning different results, the persisted data in your materialized column(s) will not automatically update to reflect these changes!
Let’s consider the function above, which performs the same calculation as the SQL expression. You may have noticed that the PL/SQL code includes a ROUND() function to ensure the result is rounded to two decimal places. Now, let’s explore what happens when we modify the function to round to three decimal places instead:
Expand/Collapse
SQL> -- Change the function to round to 3 decimal places
SQL> CREATE OR REPLACE FUNCTION calculate_total_price(
p_net_price IN NUMBER,
p_tax_rate IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN ROUND(p_net_price * (1 + p_tax_rate / 100), 3);
END;
/
Function CALCULATE_TOTAL_PRICE compiled
SQL> -- Query to check whether total_price has now 3 decimal places
SQL> SELECT product_name, net_price, total_price
FROM products;
PRODUCT_NAME NET_PRICE TOTAL_PRICE
_______________ ____________ ______________
Laptop 999.99 1099.99
Smartphone 699.99 757.74
Headphones 149.99 159.74
Tablet 499.99 541.24
Smartwatch 249.99 266.24
As you can see, the total_price column still only shows two decimal places. The only way to get around that is to re-create the materialized column:
Expand/Collapse
SQL> ALTER TABLE products DROP COLUMN total_price;
Table PRODUCTS altered.
SQL> ALTER TABLE products
ADD total_price NUMBER
GENERATED ALWAYS AS (calculate_total_price(net_price, tax_rate)) MATERIALIZED
NOT NULL;
Table PRODUCTS altered.
SQL> -- Query to check whether total_price has now 3 decimal places
SQL> SELECT product_name, net_price, total_price
FROM products;
PRODUCT_NAME NET_PRICE TOTAL_PRICE
_______________ ____________ ______________
Laptop 999.99 1099.989
Smartphone 699.99 757.739
Headphones 149.99 159.739
Tablet 499.99 541.239
Smartwatch 249.99 266.239
Likewise, you may find yourself in a surprising situation if your function were ever to be dropped:
Expand/Collapse
SQL> -- The database doesn't prevent dropping the total price calculation function
SQL> -- Even if it is used in a materialized column expression
SQL> DROP FUNCTION calculate_total_price;
Function CALCULATE_TOTAL_PRICE dropped.
SQL> -- The total_price materialized column can no longer be queried and will have to be dropped.
SQL> SELECT total_price FROM products;
Error starting at line : 1 in command -
SELECT total_price FROM products
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-00904: "GERALD"."CALCULATE_TOTAL_PRICE": invalid identifier
SQL> ALTER TABLE products DROP COLUMN total_price;
Table PRODUCTS altered.
However, this is the same behavior that virtual columns also have:
Expand/Collapse
SQL> -- Recreate the function to round to 2 decimal places
SQL> CREATE OR REPLACE FUNCTION calculate_total_price(
p_net_price IN NUMBER,
p_tax_rate IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN ROUND(p_net_price * (1 + p_tax_rate / 100), 2);
END;
/
Function CALCULATE_TOTAL_PRICE compiled
SQL> -- Recreate the table with a virtual column instead of a materialized column
SQL> -- This will not store the total_price physically but calculate it on-the-fly
SQL> -- The total_price column is defined as VIRTUAL to ensure it is calculated dynamically
SQL> CREATE TABLE products_virtual (
product_name VARCHAR2(100),
net_price NUMBER(10,2),
tax_rate NUMBER(5,2),
total_price NUMBER(10,2)
GENERATED ALWAYS AS (calculate_total_price(net_price, tax_rate)) VIRTUAL NOT NULL
);
Table PRODUCTS_VIRTUAL created.
SQL> -- Select the total_price from the products_virtual table
SQL> SELECT product_name, net_price, tax_rate
FROM products_virtual;
no rows selected
SQL> -- Drop the function that is used for the virtual column
SQL> DROP FUNCTION calculate_total_price;
Function CALCULATE_TOTAL_PRICE dropped.
SQL> -- Query the products_virtual table to see the calculated total_price
SQL> SELECT product_name, net_price, total_price
FROM products_virtual;
Error starting at line : 1 in command -
SELECT product_name, net_price, total_price
FROM products_virtual
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-00904: "GERALD"."CALCULATE_TOTAL_PRICE": invalid identifier
If you require functionality with dependency tracking for your user-defined function, you can default back to a trigger. Although the symptoms remain the same:
Expand/Collapse
SQL> -- Drop materialized column products table
SQL> ALTER TABLE products DROP COLUMN total_price;
Table PRODUCTS altered.
SQL> -- Create regular column for total_price in products table
SQL> ALTER TABLE products
ADD total_price NUMBER(10,2);
Table PRODUCTS altered.
SQL> -- Recreate the function to round to 2 decimal places
SQL> CREATE OR REPLACE FUNCTION calculate_total_price(
p_net_price IN NUMBER,
p_tax_rate IN NUMBER
)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
RETURN ROUND(p_net_price * (1 + p_tax_rate / 100), 2);
END;
/
Function CALCULATE_TOTAL_PRICE compiled
SQL> -- Create a trigger to calculate total_price before insert or update
SQL> -- using a user-defined function
SQL> CREATE OR REPLACE TRIGGER trg_calculate_total_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
BEGIN
:NEW.total_price := calculate_total_price(:NEW.net_price, :NEW.tax_rate);
END;
/
Trigger TRG_CALCULATE_TOTAL_PRICE compiled
SQL> -- Select all columns from products table
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME NET_PRICE TAX_RATE TOTAL_PRICE
_____________ _______________ ____________ ___________ ______________
1 Laptop 999.99 10
2 Smartphone 699.99 8.25
3 Headphones 149.99 6.5
4 Tablet 499.99 8.25
5 Smartwatch 249.99 6.5
SQL> -- Update total price for existing records
SQL> UPDATE products SET tax_rate = tax_rate;
5 rows updated.
SQL> -- Commit the transaction to save the changes
SQL> COMMIT;
Commit complete.
SQL> -- Select all columns from products table to verify the trigger
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME NET_PRICE TAX_RATE TOTAL_PRICE
_____________ _______________ ____________ ___________ ______________
1 Laptop 999.99 10 1099.99
2 Smartphone 699.99 8.25 757.74
3 Headphones 149.99 6.5 159.74
4 Tablet 499.99 8.25 541.24
5 Smartwatch 249.99 6.5 266.24
SQL> -- Drop the function used in the trigger
SQL> DROP FUNCTION calculate_total_price;
Function CALCULATE_TOTAL_PRICE dropped.
SQL> -- Insert new records into products table to veryify the invalid trigger
SQL> INSERT INTO products (product_name, net_price, tax_rate)
VALUES ('Monitor', 199.99, 8.25);
Error starting at line : 1 in command -
INSERT INTO products (product_name, net_price, tax_rate)
VALUES ('Monitor', 199.99, 8.25)
Error at Command Line : 1 Column : 13
Error report -
SQL Error: ORA-04098: trigger 'GERALD.TRG_CALCULATE_TOTAL_PRICE' is invalid and failed re-validation
https://docs.oracle.com/error-help/db/ora-04098/04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
*Cause: A trigger was attempted to be retrieved for execution and was
found to be invalid. This also means that compilation/authorization
failed for the trigger.
*Action: Options are to resolve the compilation/authorization errors,
disable the trigger, or drop the trigger.
More Details :
https://docs.oracle.com/error-help/db/ora-04098/
SQL> -- Recompile the trigger to ensure to retrieve errors
SQL> ALTER TRIGGER trg_calculate_total_price COMPILE;
Warning: ORA-17110: Execution completed with warning.
https://docs.oracle.com/error-help/db/ora-17110/
Trigger TRG_CALCULATE_TOTAL_PRICE altered.
SQL> -- Show errors in the trigger
SQL> SHOW ERRORS;
Errors for TRIGGER TRG_CALCULATE_TOTAL_PRICE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PL/SQL: Statement ignored
2/23 PLS-00201: identifier 'CALCULATE_TOTAL_PRICE' must be declared
SQL>
When should you use a materialized vs a virtual column?
The trade-off between materialized and virtual columns comes down to whether you want the calculation to occur during DML (INSERT or UPDATE) operations or at query time (SELECT). Your workload will determine whether a materialized or virtual column is more suitable. If your workload is query-intensive, you may benefit from using a materialized column, where the value is calculated once during DML operations and retrieved multiple times during queries. Conversely, if your workload involves frequent DML operations, a virtual column is preferable, as a materialized column would negatively impact performance by requiring the value to be calculated and persisted with each DML statement. In contrast, a virtual column only calculates the value during query time.
Conclusion
Materialized columns, like virtual columns, are a powerful mechanism for reducing dependencies between column values. Materialized columns are ideal for read-intensive workloads, while virtual columns are better suited for write-intensive workloads. For either one, it is best to use SQL expressions for the calculation of the derived value.
Pingback: What’s new for Developers in Oracle Database 23.7 – Gerald on IT