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

  • From: Karl Arao <karlarao@xxxxxxxxx>
  • To: srinivas.chintamani@xxxxxxxxx
  • Date: Sun, 31 Jan 2010 00:39:57 +0800

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
>

Other related posts: