Virtual Columns in the Oracle Database

This week's throwback feature of the week is the virtual column. Virtual Columns let you create columns that aren't stored physically but are calculated on the fly when selected.

Virtual Columns have been available in the Oracle Database since 11g, released around 2007.

Virtual Columns in Action

Since the NHL playoffs recently started, let's imagine we want to build a small app where we track shots and goals per player, and we want to calculate the shooting percentage without storing it. We'll use a virtual column in our table to hold that percentage.

CREATE TABLE nhl_stats (
  player_id NUMBER,
  player_name VARCHAR2(50),
  goals NUMBER,
  shots_on_goal NUMBER,
  shooting_pct NUMBER(5,2) GENERATED ALWAYS AS (ROUND((goals / shots_on_goal) * 100, 2)) VIRTUAL
);

The virtual column looks like a normal column in the table.

desc nhl_stats;

Now, we can add some data:

INSERT INTO nhl_stats (player_id, player_name, goals, shots_on_goal) 
VALUES  (20, 'Chris Kreider', 1, 1),
 		(93, 'Mika Zibanejad', 2, 3);

COMMIT;

We can select from the table and see the dynamically calculated shooting_pct each player.

select * from nhl_stats;

Altering Virtual Columns

Now let’s say we want to see the shooting accuracy in a decimal form instead of percentage. That’s an easy change:

ALTER TABLE nhl_stats MODIFY (shooting_pct NUMBER(5,4) AS (ROUND(goals / shots_on_goal, 4)));

Check the new values:

SELECT * FROM nhl_stats;

You can also add virtual columns to existing tables. Let's drop and re-add the virtual column with a new formula:

ALTER TABLE nhl_stats DROP COLUMN shooting_pct;
ALTER TABLE nhl_stats ADD (shooting_pct NUMBER(5,2) AS (ROUND((goals / shots_on_goal) * 100, 2)));

Other things to note

You can treat virtual columns like other columns in the database. They can be indexed, exist in the dba/user views, and you can collect stats on them. For example:

SELECT column_id, column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'NHL_STATS'
ORDER BY column_id;

For restrictions and notes on virtual columns, see the documentation.

Clean Up

You can clean up with the following

DROP TABLE nhl_stats;

Wrap-Up

Virtual columns are an easy way to dynamically derive calculations you may need without storing them on disk, all while being treated like a regular column by the database.

Until next time

-Killian