Re: Oracle 11g Virtual Columns?
Date: Tue, 20 Jan 2009 21:33:05 -0000
"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> Well, they're columns...so lots of goodies just in that, eg constraints
> (foreign key, not null, unique, primary key), optimizer stats, fgac, 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.htmlReceived on Tue Jan 20 2009 - 15:33:05 CST