Re: Oracle 11g Virtual Columns?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:C96dnZGzkNyL3uvUnZ2dnUVZ8qbinZ2d_at_bt.com...
>
> "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%.
>

Correction:
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