Re: SQL statement tuning

Yes, I did try this one and got the result as fast as without the 'OR'. The remedy application support group said that they had to contact BMC to see what they could do, because this query is only a small subset of the full query (lots of 'OR'), and a lot of other queries are like this. 

Thanks

Robin


Finn Jorgensen wrote:


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@xxxxxxx <mailto:rli@xxxxxxx>> 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






--------------------

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.

Other related posts: