Re: Query with NULL
Date: Wed, 30 Dec 2009 07:29:26 -0800 (PST)
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 188.8.131.52)
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: 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