Tuning queries with "IS NULL" / "IS NOT NULL" conditions

From: Srinivas Chintamani <srinivas.chintamani_at_gmail.com>
Date: Fri, 29 Jan 2010 20:30:07 -0500
Message-ID: <98c5e2a21001291730h48045e6vdc9adf7a61697dd3_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 29 2010 - 19:30:07 CST