Home » RDBMS Server » Performance Tuning » Performance tuning of SQL query (oracle 9i)
Performance tuning of SQL query [message #294114] Wed, 16 January 2008 09:04 Go to next message
Messages: 1
Registered: January 2008
Junior Member
Hi the follwoing query is taking lot of time.
the t_prc_master table has more than 8lakhs records.how can we tune further to get the same result set.

SELECT max(tpm2.prc_dt),
tpm2.asset_id ,
tpm2.pricing_pt_id ,tpm1.prc_dt
FROM t_prc_master tpm1,
t_prc_master tpm2,
t_pricing_pt_grp_assign ptg,
t_pricing_pt pt
WHERE to_date(to_char(tpm1.last_chg_dt,'mm/dd/YYYY'),'mm/dd/yyyy') = report_date_
AND tpm1.asset_id = tpm2.asset_id
AND tpm1.pricing_pt_id = tpm2.pricing_pt_id
AND tpm2.prc_dt < tpm1.prc_dt
AND tpm2.accept_flg = 'Y'
AND tpm1.accept_flg = 'Y'
AND tpm1.pricing_pt_id = ptg.pricing_pt_id
AND ptg.pricing_pt_id = pt.pricing_pt_id
AND ptg.pricing_pt_grp_id = pricing_pt_grp_id_
AND ( pricing_pt_id_ is null OR
pricing_pt_id_ = -1 OR
tpm1.pricing_pt_id = pricing_pt_id_ )
GROUP BY tpm2.asset_id, tpm2.pricing_pt_id,tpm1.prc_dt;

Thanks in advance.
Re: Performance tuning of SQL query [message #294118 is a reply to message #294114] Wed, 16 January 2008 09:22 Go to previous message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Read this link
http://www.orafaq.com/forum/t/84315/94420/ and come back to us again with the requested information.


Previous Topic: Help
Next Topic: Update only 8 rows in a huge Table
Goto Forum:

Current Time: Mon Dec 05 05:00:37 CST 2016

Total time taken to generate the page: 0.09017 seconds