Try using Ordered hint in the outer query
and HASH_AJ hint in the sub query.
SELECT /*+ ordered */
ORDH_NO,ORDH_DATE,ORDH_REF_NO,xxx....
FROM ORDRHEAD I,
ORDRDETL
WHERE ORDH_NO = ORDT_ORDH_NO
AND ORDH_STATUS='Y'
AND I.ORDH_NO NOT in
(SELECT /*+ hash_aj */ ORDH_NO
FROM PS_ORDH_CNTL ) ;
Do not omit any column in the Select statement.
Post the explain plan output here.
The 2nd approach is:
SELECT /*+ ORDERED */ C1, C2, C3 , ......
FROM ( SELECT /*+ NO_MERGE */
ORDH_NO, ORDH_DATE ,....
FROM ORDRHEAD H
WHERE H.ORDH_STATUS = 'Y'
AND H.ORDH_NO NOT IN
( SELECT /*+ hash_aj */ ORDH_NO
FROM PS_ORDH_CNTL)
) I,
ORDRDETL D
WHERE I.ORDH_NO = D.ORDT_ORDH_NO ;
The idea is filter the HEADER table as much as possible before join with DETAIL table.
Read some of the tuning books. That will help you in the long run.
Tamil
