RE: nvl not using index

Hi,

In Oracle 8i, you can use function based index, may be it's helpful for you. Please correct me, if it's *NOT*.

Nirmal.

> -----Original Message-----
> From: Jack C. Applewhite [SMTP:japplewhite_at_inetprofit.com]
> Sent: Tuesday, September 11, 2001 2:12 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: nvl not using index
>
> BigP,
>
> 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.
>
> Of course, dynamic SQL would work, too, but that can get pretty messy to
> write and maintain.
>
> Jack
>
> --------------------------------
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com
> japplewhite_at_inetprofit.com
> (512)327-9068
>
>
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Big
> Planet
> 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 ;
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Nirmal Kumar  Muthu Kumaran
  INET: NIRMALK_at_qtel.com.qa

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).