Did temporary thing by appplying nvl only on nullable columns, which are no=
t=20
that many. I like Tim's solution, will test it.=20
Thanks all.rm.
On 5/13/05, Tim Gorman <tim_at_evdbt.com> wrote:
> Ranko,
>=20
> 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_NAM=
E =3D
> :b1".
>=20
> 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...
>=20
> So...
>=20
> select ...
> from ...
> where column-name =3D :b1
> ...
> union all
> select ...
> from ...
> where :b1 is null
> ...
>=20
> 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 f=
ar.
>=20
> I believe the USE_CONCAT hint will automatically convert an "OR" or "IN"
> clause to a series of UNION ALL'd subqueries like this...
>=20
> Hope this helps...
>=20
> -Tim
>=20
> on 5/13/05 9:57 AM, Ranko Mosic at ranko.mosic_at_gmail.com wrote:
>=20
> > Hi,=3D20
> > I have to use NVL function ( or similar ) in where clause.=3D20
> > I need to have column accessed via index.=3D20
> > Creation of functional indexes on nvl(column_name ) can not be done
> > for all columns
> > ( political and other reasons ).=3D20
> >
> > Is there a way ?=3D20
> >
> > Regards, Ranko.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l
Received on Sat May 14 2005 - 08:21:42 CDT
