Re: Query with NULL

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 30 Dec 2009 07:36:19 -0800 (PST)
Message-ID: <c7cb46e6-d7c1-4ec2-b29c-8d5320586fa1_at_n38g2000yqf.googlegroups.com>

On Dec 29, 4:39 pm, 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

Being that :wp18 and :wp19 are program bind variables then depending on which variables have a value they are actually 4 different conditions: return all rows when neither variable has a value, return matching rows for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is null, and return only rows that match both wp18 and wp19 when both have a value.

While one query could be written to handle this set of requirements, I would consider to make the correct desired result clear for future maintenance programmers just coding a simple SQL statement for each condition within a IF structure within the program. That way, based on which variables have values the correct desired SQL statement is submitted.

I think taking the simple approach would likely greatly reduce the chance the CBO chooses anything but the optimal plan for the submitted query and again the simplier SQL will be unlikely to confuse any future maintenance programmer as to what result is desired.

Which approach is best is going to depend on the environment: additional complexity in where clause conditions, how static the application is expected to be, etc.... It may be that each approach will prove beneficial depending on the program in question.

HTH -- Mark D Powell -- Received on Wed Dec 30 2009 - 09:36:19 CST