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...

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

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

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