Home » RDBMS Server » Performance Tuning » Optimizing the Query (Oracle 10g release 2, OS Server Unix, OS Client Window XP)
Optimizing the Query [message #332388] Tue, 08 July 2008 07:32 Go to next message
Messages: 34
Registered: June 2005
Location: DUBAI, UAE

Dear Guros

Any One Suggest me to improve this Query with Best Explain. I want to this Query to populate hte Data into LOVs rECORD gROUP

SELECT job_ref, job_status, bol_number, department_code, import_export,
FROM (SELECT job_reference job_ref, job_status, bol_number,
department_code, import_export, job_reference orig_ref
FROM job_headers jh
WHERE jh.job_reference LIKE :booking.t_jobref || '%'
AND jh.job_status IN ('0', '1', '20')
AND ( jh.bol_number IS NULL
OR ((SELECT MAX (job_status)
FROM consolidation_status
WHERE consolidation_ref = jh.bol_number
AND department_code = jh.department_code
AND import_export = jh.import_export
AND event_date IS NOT NULL) = '0'
SELECT external_job_reference job_ref, job_status, bol_number,
department_code, import_export, job_reference orig_ref
FROM job_headers_light jh
WHERE jh.external_job_reference LIKE :booking.t_jobref || '%'
AND jh.job_status IN ('0', '1')
SELECT trpuh.purchase_ref job_ref, trpuh.status job_status,
trpuh.bol_number bol_number,
trpuh.department_code dedpartment_code, 'E' import_export,
trpuh.purchase_ref orig_ref
FROM purchase_header trpuh
WHERE trpuh.purchase_ref LIKE :booking.t_jobref || '%'
AND trpuh.status = '1')
ORDER BY job_ref;

Muhammad Aurangzaib
Re: Optimizing the Query [message #332389 is a reply to message #332388] Tue, 08 July 2008 07:34 Go to previous message
Michel Cadot
Messages: 65079
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

And finally please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Previous Topic: What do Oracle waits mean
Next Topic: update is slow with CLOB data
Goto Forum:

Current Time: Thu Jul 20 13:53:33 CDT 2017

Total time taken to generate the page: 0.10383 seconds