FW: nvl not using index

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Tue, 11 Sep 2001 07:13:24 -0700
Message-ID: <F001.00389918.20010911062024@fatcity.com>

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:DFarnsworth_at_Ashleyfurniture.com]
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:bigplanet34_at_hotmail.com] 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 ; Received on Tue Sep 11 2001 - 09:13:24 CDT