Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Order by clause creating performance issue.,Plz help

    Hi guys,
    The query i jad to tune was having an "OR" condition in the subquery which was creating performance issue. When i used to comment the OR condition it used to work fine. For this, i seperated the whole query using UNION ALL and putting each of the OR conditions in either of the query. Now the problem is that if i remove the order by clause from the query it works ab fine but with the order by clause it creates a serious performance issue. PLz help. The part of the query is as under:

    Select * from
    (
    SELECT
    S.PO-- as PONumber,
    S.LIN-- as LineItemNumber,
    ----(rest of the selections)
    FROM
    PS_P-- H,
    PS_PO-- L,
    PS_PO_-- S,
    PS_RECV-- R
    WHERE (H.PO-- IN ('D','C','X') OR (H.CHN-- > 0 AND H.PO-- = 'A')) AND

    ----(rest of the conditions)
    (the line below was creating performance issue at the first attempt so i seperated the statements using union all)
    AND H.LAST-- >= TO_DATE('2009-06-01-00:00:00','YYYY-MM-DD-HH24.MI.SS')
    GROUP BY S.PO--, S.LINE--, S.SCHE--, H.RECV--,
    L.--, (rest of the fields)

    UNION ALL

    SELECT
    S.PO-- as PONumber,
    S.LIN-- as LineItemNumber,
    ----(rest of the selections)
    FROM
    PS_P-- H,
    PS_PO-- L,
    PS_PO_-- S,
    PS_RECV-- R
    WHERE (H.PO-- IN ('D','C','X') OR (H.CHN-- > 0 AND H.PO-- = 'A')) AND
    ----(rest of the conditions)

    (this is how i seperated the OR condition)
    AND H.LAST-- < TO_DATE('2009-06-01-00:00:00','YYYY-MM-DD-HH24.MI.SS')
    AND TO_DATE('2009-06-01-00:00:00','YYYY-MM-DD-HH24.MI.SS') <= (SELECT MAX(RH.LAST_--)
    FROM PS_RE- RH, PS_-- RL
    WHERE (--conditions)
    )
    ORDER BY
    PONumber,
    LineItemNumber

    if i just remove the order by clause from the whole query it works ab fine... please suggest...

  2. #2
    Join Date
    Jun 2009
    Posts
    10

    try this

    SELECT col1, col2 FROM tab1
    UNION
    SELECT colA, colB from tab2
    ORDER BY col1 || NULL , col2 || NULL;

    Does this work? It should use the column names from the first part of the union query (col1, col2) above.
    The || NULL should turn off the index? Can you post an explain plan of the slow query and the fast query?

    Frank

  3. #3
    Join Date
    Jul 2009
    Posts
    2

    Unhappy

    its still the same dude... the query is getting hanged it seems... without orderby its running in arnd 15 secs

  4. #4
    Join Date
    Jun 2009
    Posts
    10

    Ok

    With the ORDER BY on can you run trace/tkprof and lets see what re-writing if any is going on?

    It is possible there is a hang, so can you tell me if you control-c the query can you run another query in the same sql plus session?

    Thanks
    Frank

Posting Permissions

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