Re: Oracle 11g Virtual Columns?
Date: Tue, 20 Jan 2009 21:40:16 -0000
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> "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%.
That would be 5% for ">", the 1% would be for "="
> 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.
Received on Tue Jan 20 2009 - 15:40:16 CST