Re: Oracle 11g Virtual Columns?

From: Jonathan Lewis <>
Date: Tue, 20 Jan 2009 21:33:05 -0000
Message-ID: <>

"Connor McDonald" <> wrote in message
> Well, they're lots of goodies just in that, eg constraints
> (foreign key, not null, unique, primary key), optimizer stats, fgac, etc
> etc

Of which, it's the optimizer stats which are the really big benefit over function-based indexes. If you write the query:

    select from emp where sal + nvl(comm,0) > 10000 then the selectivity is 1%.

If you create a function based index on (sal + nvl(comm,0)) then you have a hidden column definition, but can generate real statistics on the values - but you have to have the index when (possibly) all you really want is the stats.

If you declare a virtual column, tot_remun say, as (sal + nvl(comm,0)) then you get the stats on tot_remun without taking up any space.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Tue Jan 20 2009 - 15:33:05 CST

Original text of this message