Re: Tuning queries with "IS NULL" / "IS NOT NULL" conditions
- From: Martin Klier <usn@xxxxxxxxx>
- To: srinivas.chintamani@xxxxxxxxx
- Date: Sat, 30 Jan 2010 16:35:42 +0100
Hi,
it's just a general answer.
I just assume you have no chance to change the model, otherwise read
Stephane's mail instaed please.
The problem is, that an index can't contain NULL values by default, so
searching for NULL is somewhat expansive.
Searching NOT NULL values in a standard case is simply in comparision:
Just look up the index, all values there are not null.
But to search for NULLs, you can choose an old trick: Create a
multi-column-index, with the column you are searching with as first
column in the index. Now the index DOES contain NULLs.
Hope it helps.
Srinivas Chintamani schrieb:
> 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
--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
