Re: Query with NULL
Date: Wed, 30 Dec 2009 07:29:26 -0800 (PST)
Message-ID: <a436576a-c44d-4880-b4ee-8dae7ed9b23d_at_a21g2000yqc.googlegroups.com>
On Dec 29, 9:19 pm, "bob123" <bob..._at_gmail.com> 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 9.2.0.6)
One possible way to achieve this without the explicit usage of UNION suggested is the rewrite with NVL or DECODE as outlined here:
http://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/
Which would look something like this for your particular query:
select
*
from
bxat.no5
WHERE
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:
http://jonathanlewis.wordpress.com/2007/02/14/conditional-sql-2/
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.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Dec 30 2009 - 09:29:26 CST