Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: Oracle Query Help !

    I am trying to tune a query and i am faced with a problem. My query performs fast when i use an "equals" in the where clause .. But when i use a "LIKE" it is very slow ...

    I am using Oracle 9.2

    Query 1 :- Equals Case

    SELECT L.PO_LINE_ITEM_NO DRAWING_NUMBER, H.PO_NUMBER PO_NUMBER, L.PO_LINE_UNIT_PRICE PRICE, TO_CHAR(H.PO_START_DATE, 'MM-DD-YY') EFFECTIVE_DATE, H.PO_VENDOR_CODE_AP SUPPLIER_CODE, TRIM(U.ORACLE_HR_FIRST_NAME)||' '||TRIM(U.ORACLE_HR_LAST_NAME) BUYER_NAME, DECODE(TRIM(H.PO_STATUS), 'APPROVED', 'Active', 'Inactive') PO_TYPE, L.PO_LINE_ITEM_DESCRIPTION DESCRIPTION, H.PO_HD_SITE_CODE SITE, L.PO_LINE_COMMODITY_CODE COMMODITY_CODE, H.PO_BUYER_AP BUYER_CODE, DECODE(P.CY_POSTED_FLAG, 'P', P.CY_CUM_STD_MATL, '0') STD_COST, TO_CHAR(R.DATE_RECEIVED, 'MM-DD-YY') LAST_RECEIPT_DATE, TRIM(S.SUPPLIER_ORDPOINT_NAME) SUPPLIER_NAME, H.PO_NOTE_TO_VENDOR PO_NOTES FROM EZ.EZ_DOP_PO_HEADER H, EZ.EZ_DOP_PO_LINES L, EZ.EZ_DOP_RECEIPTS R, MTRAX.USER_ORACLEHR_XREF U, MMCOST.PAPART P, PQ.SNET_SUPP_MASTER S WHERE H.PO_NUMBER = L.PO_NUMBER AND H.PO_REVISION_NUMBER = L.PO_REVISION_NUMBER AND H.PO_NUMBER = R.PO_NUMBER(+) AND H.PO_HD_SITE_CODE = R.SITE(+) AND H.PO_BUYER_L_NUMBER = U.USERID(+) AND L.PO_LINE_ITEM_NO = P.PART_NO(+) AND H.PO_VENDOR_CODE_AP = S.SUPPLIER_ORDPOINT(+) AND H.PO_INTERFACE_SOURCE = 'GEAOP' AND L.PO_LINE_ITEM_NO ='165D6172G006' AND H.PO_REVISION_NUMBER = (SELECT MAX(PO_REVISION_NUMBER) FROM EZ.EZ_DOP_PO_HEADER WHERE PO_NUMBER = H.PO_NUMBER) AND (R.DATE_RECEIVED = (SELECT MAX(DATE_RECEIVED) FROM EZ.EZ_DOP_RECEIPTS WHERE PO_NUMBER = R.PO_NUMBER) OR 0 = (SELECT COUNT(DATE_RECEIVED) FROM EZ.EZ_DOP_RECEIPTS WHERE PO_NUMBER = R.PO_NUMBER))

    Query 2 :- LIKE CASE

    SELECT L.PO_LINE_ITEM_NO DRAWING_NUMBER, H.PO_NUMBER PO_NUMBER, L.PO_LINE_UNIT_PRICE PRICE, TO_CHAR(H.PO_START_DATE, 'MM-DD-YY') EFFECTIVE_DATE, H.PO_VENDOR_CODE_AP SUPPLIER_CODE, TRIM(U.ORACLE_HR_FIRST_NAME)||' '||TRIM(U.ORACLE_HR_LAST_NAME) BUYER_NAME, DECODE(TRIM(H.PO_STATUS), 'APPROVED', 'Active', 'Inactive') PO_TYPE, L.PO_LINE_ITEM_DESCRIPTION DESCRIPTION, H.PO_HD_SITE_CODE SITE, L.PO_LINE_COMMODITY_CODE COMMODITY_CODE, H.PO_BUYER_AP BUYER_CODE, DECODE(P.CY_POSTED_FLAG, 'P', P.CY_CUM_STD_MATL, '0') STD_COST, TO_CHAR(R.DATE_RECEIVED, 'MM-DD-YY') LAST_RECEIPT_DATE, TRIM(S.SUPPLIER_ORDPOINT_NAME) SUPPLIER_NAME, H.PO_NOTE_TO_VENDOR PO_NOTES FROM EZ.EZ_DOP_PO_HEADER H, EZ.EZ_DOP_PO_LINES L, EZ.EZ_DOP_RECEIPTS R, MTRAX.USER_ORACLEHR_XREF U, MMCOST.PAPART P, PQ.SNET_SUPP_MASTER S WHERE H.PO_NUMBER = L.PO_NUMBER AND H.PO_REVISION_NUMBER = L.PO_REVISION_NUMBER AND H.PO_NUMBER = R.PO_NUMBER(+) AND H.PO_HD_SITE_CODE = R.SITE(+) AND H.PO_BUYER_L_NUMBER = U.USERID(+) AND L.PO_LINE_ITEM_NO = P.PART_NO(+) AND H.PO_VENDOR_CODE_AP = S.SUPPLIER_ORDPOINT(+) AND H.PO_INTERFACE_SOURCE = 'GEAOP' AND L.PO_LINE_ITEM_NO LIKE '165D6172G006%' AND H.PO_REVISION_NUMBER = (SELECT MAX(PO_REVISION_NUMBER) FROM EZ.EZ_DOP_PO_HEADER WHERE PO_NUMBER = H.PO_NUMBER) AND (R.DATE_RECEIVED = (SELECT MAX(DATE_RECEIVED) FROM EZ.EZ_DOP_RECEIPTS WHERE PO_NUMBER = R.PO_NUMBER) OR 0 = (SELECT COUNT(DATE_RECEIVED) FROM EZ.EZ_DOP_RECEIPTS WHERE PO_NUMBER = R.PO_NUMBER))

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Wow, what an awful query !

    I personally would say that IT IS NORMAL : the LIKE operator is slow. If you want a fast text search, consider using an Oracle Text Index (for FULL TEXT SEARCH). For small texts (some words, a few lines maximum), CTXCAT indexes are extremely efficient (at the price of disk space and speed of DML operations though). See Oracle Text documentation for more details, and don't hesitate to ask questions, I'll be glad to help.

    Regards,

    RBARAER

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    An index on column "L.PO_LINE_ITEM_NO" will help make it a bit faster.

    Ravi

  4. #4
    Join Date
    Oct 2004
    Posts
    1

    Queries with like are simply slow

    When your query uses LIKE as the keyword, instead of =, it cannot take advantage of the index. Here is an idea.... your query slows down on this:
    AND l.po_line_item_no LIKE '165D6172G006%'
    If this query is run frequently, I think you can rewrite the query
    AND subsrt(l.po_line_item_no,1,12) = '165D6172G006'
    and then create a function-based index. It has been a while since I looked at this, I am not sure you can create a function-based index based on the substr() function. But if you intend on running that query frequently, it would be worth the effort to check it out!

    Edward Stoever -- www.database-expert.com

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Would you mind posting table details (a simple DESC would do).. I dont know if I would have wrote the query like you did.

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Actually, if a column has an index on it, then like can use an index provided the "%" sign does not occur in the beginning.

    Here's some output from SQL*Plus:

    Code:
     
    SQL> desc unique_descr;
    Name		 Null?		 Type
    ------------ --------	 ------------------------------------
    DESCR		 NOT NULL VARCHAR2(200)
     
     
    SQL> create index idx_descr on unique_descr(descr);
    Index created.
     
    SQL> set autotrace traceonly explain
     
    SQL> select * from unique_descr;
    Execution Plan
    ----------------------------------------------------------
    0	 SELECT STATEMENT Optimizer=CHOOSE
    1	0 TABLE ACCESS (FULL) OF 'UNIQUE_DESCR'
     
    SQL> select * from unique_descr where descr = 'ABC';
    Execution Plan
    ----------------------------------------------------------
    0	 SELECT STATEMENT Optimizer=CHOOSE
    1	0 INDEX (RANGE SCAN) OF 'IDX_DESCR' (NON-UNIQUE)
     
    SQL> select * from unique_descr where descr like 'ABC%';
    Execution Plan
    ----------------------------------------------------------
    0	 SELECT STATEMENT Optimizer=CHOOSE
    1	0 INDEX (RANGE SCAN) OF 'IDX_DESCR' (NON-UNIQUE)
     
    SQL> select * from unique_descr where descr like '%ABC%';
    Execution Plan
    ----------------------------------------------------------
    0	 SELECT STATEMENT Optimizer=CHOOSE
    1	0 TABLE ACCESS (FULL) OF 'UNIQUE_DESCR'
    As the execution plan clearly shows, when certain conditions are met, an index can be used effectively even with the like operator.

    Ravi

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    considering that you didn't post info about indexes, # rows, stats, explain plans or anything else, I'll take a guess:
    The column stats on PO_LINE_ITEM_NO lead the optimizer to believe that there are many values that would fulfill the like requirement (perhaps the column has a length of 50, or something like that), so it chooses to ignore the index on that column (or conversely, the LIKE causes an index to be used). You could use hints to force the LIKE sql to use the same access path as the EQUAL sql.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    The least you could do is format the damn thing for readability.
    Here is some basic formatting of yor first query:
    PHP Code:
    SELECT 
     L
    .PO_LINE_ITEM_NO DRAWING_NUMBER,
     
    H.PO_NUMBER PO_NUMBER,
     
    L.PO_LINE_UNIT_PRICE PRICE,
     
    TO_CHAR(H.PO_START_DATE'MM-DD-YY'EFFECTIVE_DATE,
     
    H.PO_VENDOR_CODE_AP SUPPLIER_CODE,
     
    TRIM(U.ORACLE_HR_FIRST_NAME)||' '||TRIM(U.ORACLE_HR_LAST_NAMEBUYER_NAME,
     
    DECODE(TRIM(H.PO_STATUS), 'APPROVED''Active''Inactive'PO_TYPE,
     
    L.PO_LINE_ITEM_DESCRIPTION DESCRIPTION,
     
    H.PO_HD_SITE_CODE SITE,
     
    L.PO_LINE_COMMODITY_CODE COMMODITY_CODE,
     
    H.PO_BUYER_AP BUYER_CODE,
     
    DECODE(P.CY_POSTED_FLAG'P'P.CY_CUM_STD_MATL'0'STD_COST,
     
    TO_CHAR(R.DATE_RECEIVED'MM-DD-YY'LAST_RECEIPT_DATE,
     
    TRIM(S.SUPPLIER_ORDPOINT_NAMESUPPLIER_NAME,
     
    H.PO_NOTE_TO_VENDOR PO_NOTES 
    FROM 
     EZ
    .EZ_DOP_PO_HEADER H,
     
    EZ.EZ_DOP_PO_LINES L,
     
    EZ.EZ_DOP_RECEIPTS R,
     
    MTRAX.USER_ORACLEHR_XREF U,
     
    MMCOST.PAPART P,
     
    PQ.SNET_SUPP_MASTER S 
    WHERE 
     H
    .PO_NUMBER L.PO_NUMBER AND
     
    H.PO_REVISION_NUMBER L.PO_REVISION_NUMBER AND
     
    H.PO_NUMBER R.PO_NUMBER(+) AND
     
    H.PO_HD_SITE_CODE R.SITE(+) AND
     
    H.PO_BUYER_L_NUMBER U.USERID(+) AND
     
    L.PO_LINE_ITEM_NO P.PART_NO(+) AND
     
    H.PO_VENDOR_CODE_AP S.SUPPLIER_ORDPOINT(+) AND
     
    H.PO_INTERFACE_SOURCE 'GEAOP' AND
     
    L.PO_LINE_ITEM_NO ='165D6172G006' AND
     
    H.PO_REVISION_NUMBER = (SELECT MAX(PO_REVISION_NUMBER
                             
    FROM EZ.EZ_DOP_PO_HEADER 
                             WHERE PO_NUMBER 
    H.PO_NUMBER) AND
     (
    R.DATE_RECEIVED = (SELECT MAX(DATE_RECEIVED
                         
    FROM EZ.EZ_DOP_RECEIPTS 
                         WHERE PO_NUMBER 
    R.PO_NUMBER
                OR 
    = (SELECT COUNT(DATE_RECEIVED
                        
    FROM EZ.EZ_DOP_RECEIPTS 
                        WHERE PO_NUMBER 
    R.PO_NUMBER)) 
    This might work better and eliminate your outer joins.
    What I worry about is that you have an outer join on EZ_DOP_RECEIPTS
    yet your where clause is dependent upon a value from this table (R.DATE_RECEIVED)
    which you then run two subqueries against this field????
    what happend when you get no data from EZ_DOP_RECEIPTS??
    exactly, the query will fail, so why even bother with the outer join to begin with?
    PHP Code:

    SELECT 
     L
    .PO_LINE_ITEM_NO DRAWING_NUMBER,
     
    H.PO_NUMBER PO_NUMBER,
     
    L.PO_LINE_UNIT_PRICE PRICE,
     
    TO_CHAR(H.PO_START_DATE'MM-DD-YY'EFFECTIVE_DATE,
     
    H.PO_VENDOR_CODE_AP SUPPLIER_CODE,
        (
    select TRIM(U.ORACLE_HR_FIRST_NAME)||' '||TRIM(U.ORACLE_HR_LAST_NAME
         
    from MTRAX.USER_ORACLEHR_XREF U
         where H
    .PO_BUYER_L_NUMBER U.USERIDBUYER_NAME,
     
    DECODE(TRIM(H.PO_STATUS), 'APPROVED''Active''Inactive'PO_TYPE,
     
    L.PO_LINE_ITEM_DESCRIPTION DESCRIPTION,
     
    H.PO_HD_SITE_CODE SITE,
     
    L.PO_LINE_COMMODITY_CODE COMMODITY_CODE,
     
    H.PO_BUYER_AP BUYER_CODE,
        (
    select DECODE(P.CY_POSTED_FLAG'P'P.CY_CUM_STD_MATL'0'
         
    from MMCOST.PAPART P
         where L
    .PO_LINE_ITEM_NO P.PART_NOSTD_COST,
     
    TO_CHAR(R.DATE_RECEIVED'MM-DD-YY'LAST_RECEIPT_DATE,
        (
    select TRIM(S.SUPPLIER_ORDPOINT_NAME
         
    from PQ.SNET_SUPP_MASTER S
         where H
    .PO_VENDOR_CODE_AP S.SUPPLIER_ORDPOINTSUPPLIER_NAME,
     
    H.PO_NOTE_TO_VENDOR PO_NOTES 
    FROM 
     EZ
    .EZ_DOP_PO_HEADER H,
     
    EZ.EZ_DOP_PO_LINES L,
     
    EZ.EZ_DOP_RECEIPTS R
    WHERE 
     H
    .PO_NUMBER L.PO_NUMBER AND
     
    H.PO_REVISION_NUMBER L.PO_REVISION_NUMBER AND
     
    H.PO_NUMBER R.PO_NUMBER(+) AND
     
    H.PO_HD_SITE_CODE R.SITE(+) AND
     
    H.PO_INTERFACE_SOURCE 'GEAOP' AND
     
    L.PO_LINE_ITEM_NO ='165D6172G006' AND
     
    H.PO_REVISION_NUMBER = (SELECT MAX(PO_REVISION_NUMBER
                             
    FROM EZ.EZ_DOP_PO_HEADER 
                             WHERE PO_NUMBER 
    H.PO_NUMBER) AND
     (
    R.DATE_RECEIVED = (SELECT MAX(DATE_RECEIVED
                         
    FROM EZ.EZ_DOP_RECEIPTS 
                         WHERE PO_NUMBER 
    R.PO_NUMBER
       OR 
    = (SELECT COUNT(DATE_RECEIVED
            
    FROM EZ.EZ_DOP_RECEIPTS 
            WHERE PO_NUMBER 
    R.PO_NUMBER)) 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2004
    Posts
    16
    If a 'unique index' is defined on your column, then if you use the '=' operator, a unique index scan is done, which is the fastest. If you use the like operator, then since a range of values can be selected, an Index-Range scan is performed which is much slower than the index-unique scan.
    Ravi, you have created only an index on the column, not a unique index. Try doing it with the unique index.. (I still havent tried it), it mught show different results.

    --Vinita

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •