In this article, we’ll explore virtual and materialized columns and @generated fields in JSON Relational Duality Views.
Why use computed columns at all?
I’ve always computed values in a query, so what’s the point?
Indexing
In Oracle, you can index a virtual or materialized column. This is pretty fun if you frequently run queries on the computed values:
create index idx_size_category on ice_cream_sales(size_category);Query simplification
Queries with computed columns are more concise. Logic is encapsulated in the schema and not repeated in the query.
Human error
Without computed columns, you manually calculate or store the same derived value in multiple places. Here’s the risk:
- Someone makes a mistake and writes the wrong computed value
- A query isn’t updated when dependent fields change, introducing errors
- Any time you make a change, you have to hunt down everywhere you compute the value and update it. Fun, right?
Types of computed columns
Virtual Columns
Introduced in Oracle Database 11g, virtual columns are defined using an expression over other columns in a table. Virtual columns are read-only, and require no storage space on disk.
Let’s check out an example, defining virtual columns on a table:
CREATE TABLE ice_cream_sales (
sale_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
flavor VARCHAR2(50),
scoops NUMBER,
price_per_scoop NUMBER(5,2),
-- Virtual column: Calculate total price automatically
total_price AS (scoops * price_per_scoop) VIRTUAL,
-- Virtual column: Automatic size category assignment
size_category AS (
CASE
WHEN scoops = 1 THEN 'Single Scoop'
WHEN scoops = 2 THEN 'Double Scoop'
WHEN scoops = 3 THEN 'Triple Threat'
ELSE 'Monster Cone'
END
) VIRTUAL
);Next, insert some data and query the virtual columns:
INSERT INTO ice_cream_sales SET
flavor = 'Mint Chip',
scoops = '2',
price_per_scoop = 3.50;
INSERT INTO ice_cream_sales SET
flavor = 'Cookies and Cream',
scoops = '3',
price_per_scoop = 2.75;
SELECT flavor, total_price, size_category
FROM ice_cream_sales;The output contains the virtual columns, computed during read:
| FLAVOR | TOTAL_PRICE | SIZE_CATEGORY |
| Mint Chip | 7 | Double Scoop |
| Cookies and Cream | 8.25 | Triple Thread |
Virtual columns are a nice enhancement if you find yourself including the same computed fields in multiple queries. Your logic can live in the table, instead of being spread around application code.
However, virtual columns come with a big downside. Because virtual columns use no storage on disk, they’re computed on read. If you frequently run queries on virtual columns, your database computes these values again and again.
This effectively trades compute time for storage space.
Materialized Columns
Enter materialized columns, introduced in Oracle Database 23.7 as an enhancement to virtual columns.
Unlike virtual columns, materialized columns are stored on disk and computed during writes. Defining a materialized column is almost identical to a virtual column – we just replace the VIRTUAL keyword with MATERIALIZED:
CREATE TABLE ice_cream_sales (
sale_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
flavor VARCHAR2(50),
scoops NUMBER,
price_per_scoop NUMBER(5,2),
-- Materialized column: compute and store total_price on disk
total_price AS (scoops * price_per_scoop) MATERIALIZED,
-- Materialized column: compute and store size_category on disk
size_category AS (
CASE
WHEN scoops = 1 THEN 'Single Scoop'
WHEN scoops = 2 THEN 'Double Scoop'
WHEN scoops = 3 THEN 'Triple Threat'
ELSE 'Monster Cone'
END
) MATERIALIZED
);Materialized columns flip the tradeoff, using more storage to save compute cycles.
And now, @generated
JSON Relational Duality Views use the @generated annotation for computed fields. If you’re not familiar, duality views add document database capabilities to your relational schemas.
@generated fields are defined using either SQL or JSONPath syntax. We’ll create a duality view that uses both:
create json relational duality view ice_cream_sales_dv
as ice_cream_sales @insert @update @delete {
_id : sale_id
flavor
scoops
pricePerScoop : price_per_scoop
-- JSONPath syntax, total_price
totalPrice @generated (path : "$.scoops * $.pricePerScoop")
-- SQL syntax, size_category
sizeCategory @generated (sql : "CASE
WHEN scoops = 1 THEN 'Single Scoop'
WHEN scoops = 2 THEN 'Double Scoop'
WHEN scoops = 3 THEN 'Triple Threat'
ELSE 'Monster Cone'
END")
};If we select a row from the view, we’ll see a JSON document like this:
{
"_id": 1,
"flavor": "Mint Chip",
"scoops": 2,
"pricePerScoop": 3.5,
"sizeCategory": "Double Scoop",
"totalPrice": 7
}Alternatively, you can include virtual or materialized columns as fields in a duality view.
However, duality views give flexibility when using computed values. Because we can have many duality views for a set of tables, different @generated fields can be created for the same data.
For example, I may have a read-write view to manage my data, and a read-only view with @generated fields for analytical queries.
When to use each type of computed column?
- VIRTUAL for values that are cheap to compute, or queried less often.
- MATERIALIZED for commonly queried values, or those that are expensive to compute.
- @generated if you need flexibility, or have many access patterns.
- If you don’t compute the value often, just include computed values in the query. No need to modify your table definitions or create views.
Leave a Reply