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_at_gmail.com 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