BigPee,
Have you tried a function-based index yet? (8i) You'll need to grant query rewrite privilege to the user creating the index.
Lisa Koivu
Glorified Typist and DBA
Ft. Lauderdale, FL, USA
-----Original Message-----
From: Farnsworth, Dave [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, September 11, 2001 8:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: nvl not using index
Big Planet wrote
�
>-I am writing this proc does a search�in database�based on these� in parameters and returns a ref cursor . Now one more�of�these�in parameters can be null and my query should return data neglecting null parameters . So I use nvl in the query as shown below
�
Big Planet, small print,,, coincidence??
-----Original Message-----
From: Big Planet [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 10, 2001 5:20 PM
To: Multiple recipients of list ORACLE-L
Subject: nvl not using index
Hi All ,
I am writing this proc does a search�in database�based on these� in parameters and returns a ref cursor . Now one more of these in parameters can be null and my query should return data neglecting null parameters . So I use nvl in the query as shown below .
Now my problem is , the query doesnt use index available on table since i m using a function .
Is there any way I can rewrite this query so that it meets my requirements and use the index . I have other option is to create a� dynamic sql based on in parameters .
TIA for any help .
-BigP
�
�
PROCEDURE get_alertlog ( p_loginid�varchar2 �,
��������������������������������������� p_startdate date,
p_firstname varchar2 ,
p_enddate date ,
p_status out number,
p_msg out varchar2,
p_refcursor out c_refcursor )
Begin
open p_refcursor for
select logpin , logtype , logaction , logdate , memberpin
FROM mem
WHERE�loginid =nvl(p_loginid , loginid �)
AND logdate between nvl(p_startdate,to_date('1-jan-1900','dd-mon-rrrr') )
And� firstname = nvl( p_firstname , firstname )
and nvl(p_enddate , sysdate ) ;
�
�
End ;
