Re: SQL statement tuning

Are you sure the execution plan is the same? Or do you mean the explain plan is the same? I find it hard to believe that a full scan of a table of 3.1 million rows would take only milliseconds.

>Can someone shed some light on how to tune this sql statement?
>Oracle version 10.2
>
>SELECT T143.C1,C490021100,C1000000001,C200000020
>FROM aradmin.T143
>WHERE (((T143.C200000020 LIKE ('Eagle' || '%')) AND (0 = 0)) OR
>('Eagle' = ' '));
>
>Execution Plan
>----------------------------------------------------------
>Plan hash value: 1317490582
>------------------------------------------------------------------------
>| Id | Operation | Name | Rows | Bytes |
>------------------------------------------------------------------------
>| 0 | SELECT STATEMENT | | 7375 | 388K|
>| 1 | TABLE ACCESS BY INDEX ROWID| T143 | 7375 | 388K|
>|* 2 | INDEX RANGE SCAN | I143_200000020_1 | 7624 | |
>------------------------------------------------------------------------
>Predicate Information (identified by operation id):
>---------------------------------------------------
> 2 - access("T143"."C200000020" LIKE 'Eagle%')
> filter("T143"."C200000020" LIKE 'Eagle%')

[snip]

>Rows Row Source Operation
>------- ---------------------------------------------------
> 1 TABLE ACCESS FULL T143 (cr=130472 pr=126166 pw=0 time=129302 us)

[snip]

>There are 3.1 million rows for this table, the "Eagle" = " " is used
>in workflow for a specific function on a remedy application. If I
>take out the 'OR'(I figure it's always not true), the query runs in
>milliseconds, the execution plan is the same . The problem is that
>we can't take the 'OR' out. How can I solve this?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l