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

If I understand your intent, you wish to Select the rows that are null on that column without doing a FTS. Yes?

Consider having a default value of "null" on the field and rather than using WHERE column IS NULL, use WHERE column=3D'null'. The index will = not
be suppressed.

Select decode(column, 'null', NULL, column)   from tableA
 where column =3D 'null'

Daniel Wittry, OCA
OCP wannabe
=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ranko Mosic Sent: Friday, May 13, 2005 8:57 AM
To: oracle-l_at_freelists.org
Subject: How to use both nvl and index access without creating functional index ?

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

Is there a way ?

Regards, Ranko.

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