Devarsh: Try using DBMS_SQLTUNE... maybe it can help...
A reader, March 03, 2011 - 8:06 pm UTC
Cheers!!
March 04, 2011 - 8:41 am UTC
Thanks Tom
Devarsh, March 03, 2011 - 9:33 pm UTC
Tom..
My apologies for this question.
I have been 'studying' oracle on this site for almost a decade now..
This was the original question that I got from a friend of mine
SELECT APSU.VENDOR_ID,
APSU.VENDOR_NAME,
APS.VENDOR_CONTACT_ID CURRENT_VENDOR_CONTACT_ID,
min(VENDOR_CONTACT_ID) over (partition by
PER_PARTY_ID,ORG_PARTY_SITE_ID) NEW_VENDOR_CONTACT_ID,
ASSA.VENDOR_SITE_ID,
ASSA.VENDOR_SITE_CODE,
HPS.PARTY_SITE_NAME,
APS.INACTIVE_DATE,
APS.FIRST_NAME,
APS.MIDDLE_NAME,
APS.LAST_NAME,
APS.PREFIX,
APS.TITLE,
APS.PHONE,
APS.PER_PARTY_ID,
APS.RELATIONSHIP_ID,
APS.REL_PARTY_ID,
APS.ORG_CONTACT_ID,
APS.ORG_PARTY_SITE_ID,
'Y' process_flag
from ap_supplier_contacts aps,
ap_suppliers apsu,
ap_supplier_sites_all assa,
hz_party_sites hps
where aps.ORG_PARTY_SITE_ID = hps.party_site_id
and assa.party_site_id = hps.party_site_id
and assa.vendor_id = apsu.vendor_id
and hps.party_id = apsu.party_id
and (per_party_id, org_party_site_id) in
(select per_party_id, org_party_site_id
from ap_supplier_contacts asuc
where ORG_PARTY_SITE_ID IN (SELECT DISTINCT PARTY_SITE_ID ROM AP_SUPPLIER_SITES_ALL ASSA2
WHERE ASSA2.creation_date between
to_date('&start_date') and to_date('&end_date'))
group by per_party_id, org_party_site_id
having count(*)>1);
The query was providing data as expected but was taking way much of time..
I re-wrote it as per my initial post..
I am aware of what you had said in your review.. have read this multiple times in your responses to such questions earlier..
I took liberty to post this question with a hope that if you could find a better way just by looking at the query..
Sorry for wasting your time..Thanks
Devarsh....
Tom N, March 07, 2011 - 9:02 am UTC
Outside of anything that Tom already covered, may I also add: Make sure your join columns are actually covered with a Foreign Key constraint... the Oracle optimizer uses those constraint definititions when figuring out how to generate the data set. Also, make sure those Foreign Key columns are indexed... finally, any where clause predicates that do value-comparisons (where x='Y')such as the assa.creation_dt should be considered for indexing, as in this case it may be able to isolate rows faster if Oracle determines table access by index is more efficient than just dumping the whole table into RAM... beyond that, without knowing the things Tom asked about, it's impossible to do much more since this query is mostly just "join these tables and give me these columns"...