Re: Oracle 11g Virtual Columns?

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:49747002.5D02_at_yahoo.com...
>
> Well, they're columns...so 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html