ORACLENERD: 11g New Feature: Virtual Columns

Not really so new as it's more than a year old...but I've finally had the opportunity to check it out. Well, opportunity at my last job which ended on Monday, but since I have so much free time now, I figured I give it a run.

Virtual Columns

Defined in the New Features doc as:

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

So what's it good for?

One thing that I see is that any derived column or expression can be placed at the table level (and indexed too!). This as opposed to putting it in a view and taking the chance that the logic is repeated exactly in every location.

So here goes my example:


CREATE TABLE virtual_columns
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY,
  first_name VARCHAR2(30),
  middle_name VARCHAR2(20),
  last_name VARCHAR2(40),
  full_name AS
  ( CASE
      WHEN middle_name IS NULL THEN
        first_name || ' ' || last_name
      WHEN middle_name IS NOT NULL THEN
        first_name || ' ' || middle_name || ' ' || last_name
    END ) VIRTUAL
);

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 1,
    'CHET',
    NULL,
    'JUSTICE' );

INSERT INTO virtual_columns
  ( id,
    first_name,
    middle_name,
    last_name )
VALUES
  ( 2,
    'MICKEY',
    'CARL',
    'MOUSE' );

CJUSTICE@>SELECT * FROM virtual_columns;

        ID FIRST_NAME MIDD LAST_NAME  FULL_NAME
---------- ---------- ---- ---------- ------------------------------
         1 CHET            JUSTICE    CHET JUSTICE
         2 MICKEY     CARL MOUSE      MICKEY CARL MOUSE

2 rows selected.

Very cool stuff indeed! One thing that might be an issue is that if someone does a SELECT * from a table without any filters. If the expression is complex (SQL --> PL/SQL context switching), it might slow it down. Of course I have no evidence to back this up yet...just pondering.

2 comments:

SydOracleJuly 30, 2008 at 11:18 PM
"If the expression is complex it might slow it down."
Should be any different from the same expression in a view or a query. However if you are collecting stats on the column (and is there a point to them if you aren't) then it may impact the duration of the stats gathering.
One thing I noted, now I've used it live, is that you can't do an INSERT INTO table SELECT or INSERT INTO table VALUES.
Because the virtual column is derived it can't be set manually, so you have to explicitly define all the columns you are allowed to set.
That can make it a pain if you are in the habit of defining PL/SQL records as TABLE%ROWTYPE. I'd recommend a view on the table that excludes the virtual column(s) so your inserts can be done through the view.
oraclenerdJuly 31, 2008 at 9:52 AM
That makes sense (same as a view or query). Not having used it in production yet, I don't know what the pitfalls might be.

And defining the %ROWTYPE is a good example of how it might affect future programming...

Thanks for the pointers Gary.

chet

Wednesday, July 30, 2008

11g New Feature: Virtual Columns

2 comments: