Home » RDBMS Server » Performance Tuning » How to Optimize the Query Without Using IN opertator
How to Optimize the Query Without Using IN opertator [message #509174] Thu, 26 May 2011 06:56 Go to next message
dhathri
Messages: 2
Registered: May 2011
Junior Member
SELECT B.FRD_dd_ref_id,
B.FRD_dd_doc_id AS DD_document_id,
B.FRD_quest_flag,
B.FRD_ref_etal_ind,
A.DD_document_number,
A.DD_document_seq,
A.DD_record_type,
A.DD_alt_document_number,
A.DD_document_date,
A.DD_modified_date,
A.DD_document_type,
A.DD_input_document_type,
A.DD_amount,
A.DD_anr_flag,
A.DD_loan_type,
A.DD_quest_flag,
A.DD_title_company,
A.DD_remarks,
A.DD_retrieve_name_ind,
A.DD_skeleton_ref_flag,
A.DD_cmt_id,
A.DD_alt_cmt_id,
A.DD_fee_number

FROM NEO.AZ07.FCT_ref_document B,
NEO.AZ07.DIM_document A
WHERE B.FRD_dd_doc_id = @in_DdDocumentId AND B.FRD_dd_ref_id in (SELECT MAX(C.FRD_dd_ref_id) FROM NEO.AZ07.FCT_ref_document C, NEO.AZ07.DIM_document D WHERE C.FRD_dd_doc_id = @in_DdDocumentId AND D.DD_document_id=C.FRD_dd_ref_id GROUP BY D.DD_document_number) AND A.DD_document_id = B.FRD_dd_ref_id ORDER BY A.DD_document_date, A.DD_document_number, A.DD_document_seq


HI i want to optimize this query as it is taking 3 sec to execute i want to reduce its execution time is there any optimization to this query please advise

Report message to a moderator

How to Optimize the Query Without Using IN opertator [message #509177 is a reply to message #509174] Thu, 26 May 2011 07:01 Go to previous messageGo to next message
dhathri
Messages: 2
Registered: May 2011
Junior Member
SELECT B.FRD_dd_ref_id,
B.FRD_dd_doc_id AS DD_document_id,
B.FRD_quest_flag,
B.FRD_ref_etal_ind,
A.DD_document_number,
A.DD_document_seq,
A.DD_record_type,
A.DD_alt_document_number,
A.DD_document_date,
A.DD_modified_date,
A.DD_document_type,
A.DD_input_document_type,
A.DD_amount,
A.DD_anr_flag,
A.DD_loan_type,
A.DD_quest_flag,
A.DD_title_company,
A.DD_remarks,
A.DD_retrieve_name_ind,
A.DD_skeleton_ref_flag,
A.DD_cmt_id,
A.DD_alt_cmt_id,
A.DD_fee_number

FROM NEO.AZ07.FCT_ref_document B,
NEO.AZ07.DIM_document A
WHERE B.FRD_dd_doc_id = @in_DdDocumentId
AND B.FRD_dd_ref_id in (SELECT MAX(C.FRD_dd_ref_id) FROM NEO.AZ07.FCT_ref_document C, NEO.AZ07.DIM_document D
WHERE C.FRD_dd_doc_id = @in_DdDocumentId AND D.DD_document_id=C.FRD_dd_ref_id
GROUP BY D.DD_document_number)
AND A.DD_document_id = B.FRD_dd_ref_id
ORDER BY A.DD_document_date,
A.DD_document_number,
A.DD_document_seq

Report message to a moderator

Re: How to Optimize the Query Without Using IN opertator [message #509178 is a reply to message #509174] Thu, 26 May 2011 07:01 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Previous Topic: Tuning sub queries with function call
Next Topic: Unable to avoid Indexes (2 Merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 05:37:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.