Re: SQL statement tuning

How about :

SELECT T143.C1,C490021100,C1000000001,C200000020 FROM aradmin.T143
WHERE ((T143.C200000020 LIKE ('Eagle' || '%')) AND (0 = 0)) union
SELECT T143.C1,C490021100,C1000000001,C200000020 FROM aradmin.T143
WHERE ('Eagle' = ' ');

Finn

On 11/27/07, Robin Li <rli_at_nyp.org> wrote:
>
> -- Hi all,
>
> 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%')
>
> From the trace:
>
> SELECT T143.C1,C490021100,C1000000001,C200000020
> FROM aradmin.T143
> WHERE (((T143.C200000020 LIKE (:"SYS_B_0" || :"SYS_B_1")) AND
> (:"SYS_B_2" = :"SY
> S_B_3")) OR (:"SYS_B_4" = :"SYS_B_5"))
>
> call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0
> 0 0
> Execute 1 0.00 0.00 0 0
> 0 0
> Fetch 2 12.10 42.49 126166 130472
> 0 1
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 4 12.10 42.49 126166 130472
> 0 1
> Misses in library cache during parse: 0
> Optimizer mode: ALL_ROWS
> Parsing user id: 51
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 TABLE ACCESS FULL T143 (cr=130472 pr=126166 pw=0 time=129302 us)
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 2 0.00
> 0.00
> db file sequential read 824 0.03
> 1.10
> db file scattered read 8641 0.06
> 33.40
> SQL*Net message from client 2 0.00
> 0.00
>
> 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?
>
> TIA
>
> Robin
>
>
>
>
>
> --------------------
>
> This electronic message is intended to be for the use only of the named
> recipient, and may contain information that is confidential or
> privileged. If you are not the intended recipient, you are hereby notified
> that any disclosure, copying, distribution or use of the contents of this
> message is strictly prohibited. If you have received this message in error
> or are not the named recipient, please notify us immediately by contacting
> the sender at the electronic mail address noted above, and delete and
> destroy all copies of this message. Thank you.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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