RE: How to use both nvl and index access without creatingfunctional index ?

The most frequent processing need I observe in the field when this question comes up is that someone is trying to find the "new" rows that don't have something akin to a "processed-on" date or a status.

Tim was exactly on point in regard to the OP's post. (That is probably a redundancy.)

In the off chance the OP is needing to find the "needs work" rows, it can often be accomplished by establishing a default value for new rows that is not within the domain actually populated by processing (if such a value reliably exists).

Even better, IMHO is starting with a non-null value and punching it to null as the rows no longer need attention (unless the particular attention is getting legitimate values into the column.)

Oracle's e-Business suite's use of WHO columns can be bent to this requirement.

And finally, just curious, if you need to see the rows with NULL in this column and needing to process them is *NOT* your purpose, just what is the reason? I have trouble realizing a different legitimate purpose that would drive a desire to search out the rows with NULL in a particular column.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tim Gorman Sent: Friday, May 13, 2005 5:13 PM
To: oracle-l_at_freelists.org
Subject: Re: How to use both nvl and index access without creatingfunctional 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


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