|
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 , memberpinFROM memWHERE 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 ; |
- RE: nvl not using index Big Planet
- RE: nvl not using index Jack C. Applewhite
- RE: nvl not using index Nirmal Kumar Muthu Kumaran
- RE: nvl not using index Farnsworth, Dave
- FW: nvl not using index Koivu, Lisa
- nvl not using index Big Planet
