Home » RDBMS Server » Performance Tuning » improving query performance (oracle 10g)
improving query performance [message #397928] Tue, 14 April 2009 10:26 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
select /*+ parallel(con,2) parallel(CON_DET,2) parallel(colh,2) */
con.LE_CODE LE_Code,
con.LE_NAME LE_Name,
COLH.LOB_CODE Organisation_LOB_Code,
COLH.LOB_DESC Organisation_LOB_Description,
COLH.SHORT_LOB_DESC Org_LOB_Short_Desc,
CON_DET.CON_FIRST_NAME Contact_First_Name,
CON_DET.CON_LAST_NAME Contact_Last_Name,
CON_DET.CON_TEL_NUMBER Contact_Telephone_Number,
CON_DET.CON_EMAIL_ADDRESS Contact_Email_Address,
CON_DET.CREATED Contact_Created_Date,
CON_DET.CON_CREATED_BY Contact_Created_By,
CON_DET.CON_TITLE Contact_Title,
case CON_DET.PHONE_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.PHONE_CONSENT end Phone_Consent,
case CON_DET.EMAIL_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.EMAIL_CONSENT end  Email_Consent,
case CON_DET.ADDRESS_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.ADDRESS_CONSENT end Address_Consent,
CON_DET.PRIMARY_CONTACT Primary_Contact,
CON_DET.RTC RTC,
CON_DET.CON_ROW_ID Con_Rowid
from
CONTACT CON,
DETAILS CON_DET,
COLH COLH
where
CON.ROW_ID = con_det.CON_ROW_ID AND
COLH.ROW_ID(+) = con_det.CON_ORG_ID


Hi

Can you please let me know how can i modify the query so that the performance of the query improves.

The count of this query fetches 1.4 million recs and it takes 20-25 mins for getting the output.

Indexes are present in the tables and are in proper use.
Re: improving query performance [message #397955 is a reply to message #397928] Tue, 14 April 2009 12:29 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please let me know how can i modify the query so that the performance of the query improves.

In the best way.
About 300 messages and you still don't know how to post. I'm not surprise you are unable to optimize a query.

Regards
Michel

[Updated on: Tue, 14 April 2009 12:29]

Report message to a moderator

Previous Topic: Explain Plan
Next Topic: want to change snap level in 10g r2
Goto Forum:
  


Current Time: Thu Dec 08 20:21:54 CST 2016

Total time taken to generate the page: 0.05506 seconds