Re: Query with NULL

From: Randolf Geist <>
Date: Wed, 30 Dec 2009 07:29:26 -0800 (PST)
Message-ID: <>

On Dec 29, 9:19 pm, "bob123" <> wrote:
> I have a lot of queries like below:
> select * from bxat.no5
> WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
> AND (tbname = :wp19 OR :wp19 IS NULL)
> so an access full on no5
> How can I speed up this query ?
> Thanks in advance
> (Oracle

One possible way to achieve this without the explicit usage of UNION suggested is the rewrite with NVL or DECODE as outlined here:

Which would look something like this for your particular query:




        no0_session_id = nvl(:wp18, no0_session_id)
AND     tbname = nvl(:wp19, tbname)

The optimizer can then transform this using a concatenation transformation to make potential use of an index.

Note however that this optimisation doesn't perform multiple concatenation transformations for multiple similar predicates, as can be seen here:

so it might not work that well in your particular case.

and you need to be aware that this re-write is not exactly the same as your statement if the columns are nullable since the re-write filters out any rows where the column is null whereas your original statement does not.


Oracle related stuff blog:

Co-author of the forthcoming "OakTable Expert Oracle Practices" book: Received on Wed Dec 30 2009 - 09:29:26 CST

Original text of this message