Re: Query with NULL
From: jefftyzzer <jefftyzzer_at_sbcglobal.net>
Date: Tue, 5 Jan 2010 19:13:12 -0800 (PST)
Message-ID: <372359e1-47b6-41a0-8648-53e13613525b_at_b2g2000yqi.googlegroups.com>
On Jan 5, 4:32 am, "Gerard H. Pille" <ghpi..._at_hotmail.com> wrote:
> 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".
>
> a. performance (saves an expensive SORT)
> B. to get all rows, even if there are duplicates
Date: Tue, 5 Jan 2010 19:13:12 -0800 (PST)
Message-ID: <372359e1-47b6-41a0-8648-53e13613525b_at_b2g2000yqi.googlegroups.com>
On Jan 5, 4:32 am, "Gerard H. Pille" <ghpi..._at_hotmail.com> wrote:
> 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".
>
> a. performance (saves an expensive SORT)
> B. to get all rows, even if there are duplicates
With respect, while I readily agree that UNION ALL is likely faster given that it obviates the need for a duplicate-eliminating sort, I think the UNION is necessary precisely because it eliminates duplicates. My goal was to suggest an alternate syntax that was semantically equivalent to the OP's original query, which wouldn't have returned duplicate rows given that it made only a single pass through the "bxat.no5" table (whether the "bxat.no5" table contains duplicates is a different matter altogether).
Regards,
--Jeff Received on Tue Jan 05 2010 - 21:13:12 CST