Re: How to use both nvl and index access without creating functional index ?

Ranko,

If you want to use an index, then you must have a non-null value in hand to
use.  Therefore, you just use the index with a predicate like "COLUMN_NAME =
:b1".

If you don't have a non-null value in hand to use with that column, then you
should use other criteria or just do a FULL table scan...

So...

    select  ...
    from    ...
    where   column-name = :b1
    ...
    union all
    select  ...
    from    ...
    where   :b1 is null
    ...

So, the two cases are "glued together" with the either-or logic that the
bind-variable ":b1" either has a non-null value or it doesn't.  If it has a
non-null value, then the first subquery runs and the second one doesn't.  If
it doesn't, then the second subquery runs and the first one doesn't get far.

I believe the USE_CONCAT hint will automatically convert an "OR" or "IN"
clause to a series of UNION ALL'd subqueries like this...

Hope this helps...

-Tim


on 5/13/05 9:57 AM, Ranko Mosic at ranko.mosic@xxxxxxxxx wrote:

> Hi,=20
> I have to use NVL function ( or similar ) in where clause.=20
> I need to have column accessed via index.=20
> Creation of functional indexes on nvl(column_name ) can not be done
> for all columns
> ( political and other reasons ).=20
> 
> Is there a way ?=20
> 
> Regards, Ranko.
> --
> http://www.freelists.org/webpage/oracle-l
> 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: