Tuning queries with "IS NULL" / "IS NOT NULL" conditions
- From: Srinivas Chintamani <srinivas.chintamani@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Jan 2010 20:30:07 -0500
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
Other related posts:
