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

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 13 May 2005 15:12:41 -0600


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 =

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


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


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


Other related posts: