nvl not using index

[Big Planet]

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: Big Planet To: 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 ( 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 ;