Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: query peforming badly with order by

    Hi All

    The below query is not working fine when used with order by clause.when executing it is taking around 5 mins.Suppose if we remove the order by clause ,then it is returning the rows in 6 seconds.I have checked the explain plans for both the scenarios.(ie with and with out the order by clause).There is no difference in the explain plans.

    Could any body give some suggestions.
    the databse version is 9.2.0.8

    Sql query

    Code:
     select a.pocust,a.level1, a.level2,a.level3, a.level4,a.ponum, a.labelnum, a.mrnum, a.modtime, a.C2CUSERNAME, a.eng_length, a.eng_width,a.end_height, a.eng_net, a.proj,a.trans,a.vend, a.manu    from (SELECT MR.PURCHASE_ORDER_CUSTOMER_NO as pocust,
      ILO.LOCATION_LEVEL_1                     as level1  ,
      ILO.LOCATION_LEVEL_2                      as level2 ,
      ILO.LOCATION_LEVEL_3                       as level3,
      ILO.LOCATION_LEVEL_4                       as level4,
      ILO.LABEL_NUMBER                          as labelnum ,
      MR.PURCHASE_ORDER_NUMBER                   as ponum,
      MR.RECEIVING_NUMBER
      ||mr.PACKAGE_NUMBER AS mrnum,
      MR.MODIFY_DATETIME_STAMP    as modtime,
      uc.FIRST_NAME
      ||' '
      ||uc.LAST_NAME C2CUSERNAME,
      RI.ENGLISH_LENGTH         eng_length,
      RI.ENGLISH_WIDTH          eng_width,
      RI.ENGLISH_HEIGHT         end_height,
      RI.ENGLISH_NET_WEIGHT     eng_net,
      MR.PROJECT_NUMBER         proj,
      MR.TRANSPORTATION_METHOD  trans,
      MR.VENDOR_NUMBER          vend,
      MR.MANUFACTURER_NO as manu
          FROM Materialreceipt mr
       JOIN useraccount uc
            ON (UC.COMPANY_CODE = MR.COMPANY_CODE
       AND UC.SITE_CODE         = MR.SITE_CODE
       AND UC.ID                = MR.MODIFY_USER_ID )
       JOIN receivingitem RI
            ON (RI.COMPANY_CODE = MR.COMPANY_CODE
       AND RI.SITE_CODE         = MR.SITE_CODE
       AND RI.RECEIVING_NUMBER  = MR.RECEIVING_NUMBER
       AND RI.PACKAGE_NUMBER    = MR.PACKAGE_NUMBER )
       JOIN Inventorylocation ILO
            ON (ILO.COMPANY_CODE = RI.INVENTORY_LOC_COMPANY_CODE
       AND ILO.SITE_CODE         = RI.INVENTORY_LOC_SITE_CODE
       AND ILO.LABEL_NUMBER      = RI.INVENTORY_LOC_LABEL_NUMBER )
         WHERE mr.COMPANY_CODE      = 'EGL'
       AND mr.SITE_CODE             = 'DT'
       AND mr.PURCHASE_ORDER_CUSTOMER_NO LIKE COALESCE('DCXMOPAR','%')
    and (
            ('' is null 
       and   (MR.PROJECT_NUMBER is null
        or    MR.PROJECT_NUMBER like '%'
             )
            )
        or  ('' is not null 
             and MR.PROJECT_NUMBER = ''
            )
           )
       AND ILO.LOCATION_LEVEL_1 like coalesce('','%')
       and (
            ('' is null
       and   (ILO.LOCATION_LEVEL_2 is null
        or    ILO.LOCATION_LEVEL_2 like '%'
             )
            )
        or  ('' is not null
       and   ILO.LOCATION_LEVEL_2 = ''
            )
           )
       and (
            ('' is null
       and   (ILO.LOCATION_LEVEL_3 is null
        or    ILO.LOCATION_LEVEL_3 like '%'
             )
            )
        or  ('' is not null
       and   ILO.LOCATION_LEVEL_3 = ''
            )
           )
       and (
            ('' is null
       and   (ILO.LOCATION_LEVEL_4 is null
        or    ILO.LOCATION_LEVEL_4 like '%'
             )
            )
        or  ('' is not null
       and   ILO.LOCATION_LEVEL_4 = ''
            )
           )) a
    order by a.level1, 
    a.level2,
    a.level3, 
    a.level4;
    Explain plan
    ---------
    Code:
    --------------------------------------------------------------------------------------
    | Id  | Operation                       |  Name              | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                    |     1 |   193 |   575 |
    |   1 |  SORT ORDER BY                  |                    |     1 |   193 |   575 |
    |   2 |   NESTED LOOPS                  |                    |     1 |   193 |   562 |
    |   3 |    NESTED LOOPS                 |                    |     1 |   161 |   560 |
    |   4 |     HASH JOIN                   |                    |    12 |  1356 |   536 |
    |   5 |      TABLE ACCESS FULL          | USERACCOUNT        |    58 |  1798 |    13 |
    |   6 |      TABLE ACCESS BY INDEX ROWID| MATERIALRECEIPT    |   985 | 80770 |   522 |
    |   7 |       INDEX SKIP SCAN           | MRHDR_IX_5         |    21 |       |   293 |
    |   8 |     TABLE ACCESS BY INDEX ROWID | RECEIVINGITEM      |     1 |    48 |     2 |
    |   9 |      INDEX UNIQUE SCAN          | RCITM_PK_1         |     1 |       |     1 |
    |  10 |    TABLE ACCESS BY INDEX ROWID  | INVENTORYLOCATION  |     1 |    32 |     2 |
    |  11 |     INDEX UNIQUE SCAN           | INLOC_PK_1         |     1 |       |     1 |
    --------------------------------------------------------------------------------------

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    When you save it takes 6 seconds are you saying it takes 6 seconds to return ALL rows or just to start returning any rows?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have checked the explain plans for both the scenarios.(ie with and with out the order by clause).There is no difference in the explain plans.
    Really?
    Both have "SORT ORDER BY"?
    Explain plan
    ---------
    Code:
    --------------------------------------------------------------------------------------
    | Id  | Operation                       |  Name              | Rows  | Bytes | Cost  |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                | ...
    |   1 |  SORT ORDER BY                  |                    |     1 |   193 |  
    ...

  4. #4
    Join Date
    Sep 2012
    Posts
    2
    Quote Originally Posted by beilstwh View Post
    When you save it takes 6 seconds are you saying it takes 6 seconds to return ALL rows or just to start returning any rows?
    It is taking 6 seconds just for returning rows


    Thanks
    Veera

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    An inherent difference exists between query with ORDER BY clause & same without.
    when no ORDER BY clause exists, the DB engine can start returning rows to the client as they are initially obtained.

    when ORDER BY clause exists the whole result set must be obtained & SORTED before any row can be shipped to the client.

    so a performance difference is to be expected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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