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.

Labels: 11g, database, development, oracle

  ¶ 7/30/2008 07:08:00 PM