RE: nvl not using index

From: Farnsworth, Dave <DFarnsworth_at_Ashleyfurniture.com>
Date: Tue, 11 Sep 2001 05:08:39 -0700
Message-ID: <F001.00389583.20010911040521@fatcity.com>

Big
Planet wrote
<FONT color=#000000
size=1> 
>-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??

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Big Planet   [mailto:bigplanet34_at_hotmail.com]Sent: Monday, September 10, 2001   5:20 PMTo: Multiple recipients of list ORACLE-LSubject:   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 <FONT 
  color=#0000f0>, 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 Tue Sep 11 2001 - 07:08:39 CDT