Prev: Materialized Views and Paritions in fact tables
Next: Oracle 10g RAC on IBM DS 3400 ?
From: bob123 on 29 Dec 2009 15:19
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)


From: jefftyzzer on 29 Dec 2009 16:39
On Dec 29, 12:19 pm, "bob123" <bob...(a)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
From: vsevolod afanassiev on 29 Dec 2009 19:33
Let's look at simplified query:

select * from bxat.no5
WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)

We assume that there is an index on "no0_session_id" and this index
has good selectivity.

If :wp18 has a value then we want the query to use index. If :wp18 is
NULL then all rows in the table
satisfy WHERE clause so we want the query to use full table scan.


Let's simplify the query even further and look at the plans:

SQL> select * from bxat.no5 where no0_session_id = :wp18;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1
Bytes=13)
2 1 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3
Card=1)


SQL> select * from bxat.no5 where :wp18 is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=42845620
Bytes=85691240)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=42845620
Bytes=85691240)


Note FILTER above. You get the same plan if you run query with
condition "1=0". While plan contains full table scan Oracle doesn't
actually run it if condition is false - it is stopped by FILTER.

Now the original simplified query:

SQL> select * from bxat.no5 where no0_session_id = :wp18 or :wp18 is
null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=2142282
Bytes=27849666)
1 0 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=2142282
Bytes=27849666)

Here "FILTER" disappeared, so Oracle always runs the full scan.

Finally the version with UNION:

SQL> select * from bxat.no5 where no0_session_id = :wp18
2 union
3 select * from bxat.no5 where :wp18 is null;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156184 Card=42845621
Bytes=85691253)
1 0 SORT (UNIQUE) (Cost=156184 Card=42845621 Bytes=85691253)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1
Bytes=13)
4 3 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3
Card=1)
5 2 FILTER
6 5 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653
Card=42845620 Bytes=85691240)

So the trick is to keep the FILTER.



From: Mark D Powell on 30 Dec 2009 10:36
On Dec 29, 4:39 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
> On Dec 29, 12:19 pm, "bob123" <bob...(a)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 --




From: bob123 on 31 Dec 2009 03:58
OK thanks all ...
The problem is that i can't rewrite the code
it's a third party software ...
any clue ?


 |  Next  |  Last
Pages: 1 2 3 4
Prev: Materialized Views and Paritions in fact tables
Next: Oracle 10g RAC on IBM DS 3400 ?