Re: Query with NULL

From: Gerard H. Pille <ghpille_at_hotmail.com>
Date: Tue, 5 Jan 2010 04:32:35 -0800 (PST)
Message-ID: <71b9e9ff-c4ad-4e6f-8670-f100fcd670f4_at_v25g2000yqk.googlegroups.com>

On 29 dec 2009, 22:39, jefftyzzer <jefftyz..._at_sbcglobal.net> wrote:
> On Dec 29, 12:19 pm, "bob123" <bob..._at_gmail.com> wrote:
>
> > Hi,
>
> > 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)
>
> Bob,
>
> If the issue is that the optimizer is choking on the ORs you might,
> barring adding indexes, want to consider changing your query from the
> present set of ORs to a set of simpler UNIONs, e.g.,
>
> select *
> from bxat.no5
> WHERE no0_session_id = :wp18
> UNION
> select *
> from bxat.no5
> WHERE
> wp18 IS NULL
> UNION
> .
> .
> .
>
> --Jeff

That should be a "UNION ALL".

1. performance (saves an expensive SORT)
2. to get all rows, even if there are duplicates

Received on Tue Jan 05 2010 - 06:32:35 CST