You may want to check on the links below... Translated Version: http://translate.google.com/translate?js=y&prev=_t&hl=en&ie=UTF-8&layout=1&eotf=1&u=http://www.oraclefans.cn/forum/showtopic_tree.jsp%3Fboardcode%3Do3%26hit%3D641%26rootid%3D66&sl=auto&tl=en Original Link: http://www.oraclefans.cn/forum/showtopic_tree.jsp?boardcode=o3&hit=641&rootid=66 Collected: http://karlarao.wordpress.com/2009/12/31/50-sql-performance-optimization-scenarios/ On Sat, Jan 30, 2010 at 9:30 AM, Srinivas Chintamani < srinivas.chintamani@xxxxxxxxx> wrote: > Hi Listers, > Can you please share your thoughts about how to go about tuning queries > that use "IS NULL" / "IS NOT NULL" conditions in where clause? > > I have a million row table similar to the one shown below and a search > procedure that as indicated below. The table was built long time ago and > there is a LOT of existing data and making any of the NULL columns NOT NULL > is just not possible. > > I will very much appreciate any ideas about how to re-write the query to be > more efficient. > > CREATE TABLE sales > ( id NUMBER > , Ent_id NUMBER > , rep VARCHAR2(30) > , client VARCHAR2(30) > , amount NUMBER > , sale_name VARCHAR2(50) > , CONSTRAINT sales_pk PRIMARY KEY (id) > ); > > CREATE OR REPLACE PROCEDURE search_sales > ( i_Ent_id IN sales.Ent_id%TYPE > , i_include_Ent IN VARCHAR2 > , i_include_rep IN VARCHAR2 > , i_include_client IN VARCHAR2 > , i_search_string IN VARCHAR2 > , o_matches OUT SYS_REFCURSOR > ) AS > l_search_string varchar2(100); > BEGIN > l_search_string := LOWER(i_search_string) || '%'; > OPEN o_matches FOR > SELECT id, sale_name > FROM sales > WHERE sale_name LIKE l_search_string > AND ( > ( > i_include_Ent = 'Y' > AND Ent_id = i_Ent_id > AND rep IS NULL > AND client IS NULL > ) > OR > ( > i_include_rep = 'Y' > AND Ent_id = i_Ent_id > AND rep IS NOT NULL > AND client IS NULL > ) > OR > ( > i_include_client = 'Y' > AND rep IS NULL > AND client IS NOT NULL > ) > ); > END search_sales; > / > > show errors > > -- > Regards, > Srinivas Chintamani >