-
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
-
Hi guys.
What i know is, if you have an index on the table
selecting COUNT(*) will scan the index in order to count rows instead of scan the table itself, that way it will be faster than selecting columns. (It's written in documentation).
Regards.
-
Originally posted by SANJAY_G
First of all, did you find what is the differnce between "select col1, col2.." and "select count(*).." ???
Your execution plans tell it all.
I just started going through Explain Plans..
Thanks for your guidances..
Your posts inducing me to study..
Thanks
-
Originally posted by engiri
Your posts inducing me to study..
Jeff Hunter
-
Originally posted by engiri
I just started going through Explain Plans..
Thanks for your guidances..
Your posts inducing me to study..
Thanks
this is what you get when you outsource...
OCP DBA 8i
ocpwannabe@yahoo.com
-----------------------------
When in doubt, pick 'C'.
-
this is what you get when you outsource...
???
Cheers!
OraKid.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|