improving query performance [message #397928] |
Tue, 14 April 2009 10:26 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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
|
|
|