nvl not using index

From: Big Planet <bigplanet34_at_hotmail.com>
Date: Wed, 12 Sep 2001 14:21:41 -0700
Message-ID: <F001.0038C612.20010912143259@fatcity.com>

what surprise me more  is that if i use where clause as ( p_loginid is null or p_loginid=loginid ), it  does not use index as well .
Why ??
 
 

----- Original Message -----
From: <A
title=bigplanet34_at_hotmail.com href="mailto:bigplanet34_at_hotmail.com">Big Planet
To: <A title=ORACLE-L_at_fatcity.com
href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

Sent: Monday, September 10, 2001 2:17 PM 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 <FONT
color=#0000f0 size=2>( p_loginid varchar2
 <FONT color=#0000f0

size=2>,
<FONT

color=#0000f0>                                        

p_startdate date<FONT
color=#0000f0>,
p_firstname varchar2 ,
p_enddate date <FONT
color=#0000f0>,
p_status <FONT

color=#0000f0>out <FONT 
color=#ff0000>number,<FONT 
color=#000000>

p_msg <FONT

color=#0000f0>out <FONT 
color=#ff0000>varchar2,<FONT 
color=#000000>

p_refcursor <FONT
color=#0000f0>out c_refcursor )
Begin
open p_refcursor for <FONT
color=#000000>
select logpin <FONT

color=#0000f0>, logtype <FONT 
color=#0000f0>, logaction <FONT 
color=#0000f0>, logdate <FONT 
color=#0000f0>, memberpin <FONT 
color=#0000f0>

FROM mem
WHERE loginid
=nvl<FONT
color=#0000f0>(p_loginid ,
loginid  <FONT
color=#0000f0>)
AND logdate
between nvl<FONT

color=#0000f0>(p_startdate<FONT 
color=#0000f0>,to_date<FONT 
color=#0000f0>('1-jan-1900'<FONT 
color=#0000f0>,'dd-mon-rrrr'<FONT 
color=#0000f0>) <FONT 
color=#0000f0>)

And  firstname = nvl( p_firstname , firstname )
and nvl<FONT

color=#0000f0>(p_enddate <FONT 
color=#0000f0>, <FONT 
color=#0000f0>sysdate <FONT 
color=#0000f0>) <FONT 
color=#0000f0>;

 

<FONT color=#000000
size=1> 
End ; Received on Wed Sep 12 2001 - 16:21:41 CDT