RE: nvl not using index

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Mon, 10 Sep 2001 14:57:47 -0700
Message-ID: <F001.00388DDD.20010910151206@fatcity.com>

<SPAN

class=546510322-10092001>BigP,
<SPAN

class=546510322-10092001> 
You
could have an If Then ElsIf...Else statement that tested the input arguments of interest (looks like p_loginid and p_firstname), each part having an appropriate Select that did not use NVL if that input argument was Not Null.  In essence, you'd be tailoring your Ref Cursor to the input argument that caused the most useful index to be used.  
Right
now it looks like just three Selects might do it.  One if p_loginid was not null, one if p_firstname was not null, and one if both were null.
<SPAN

class=546510322-10092001> 
Of
course, dynamic SQL would work, too, but that can get pretty messy to write and maintain.
<SPAN

class=546510322-10092001> 
<SPAN

class=546510322-10092001>Jack

--------------------------------Jack C. 

ApplewhiteDatabase Administrator/DeveloperOCP Oracle8 DBAiNetProfit, Inc.Austin,
Texaswww.iNetProfit.comjapplewhite_at_inetprofit.com(512)327-9068

  <FONT face=Tahoma
  size=2>-----Original Message-----From: root_at_fatcity.com   [mailto:root_at_fatcity.com]On Behalf Of Big PlanetSent:   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>;

  End
; Received on Mon Sep 10 2001 - 16:57:47 CDT