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
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.
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
