Home » SQL & PL/SQL » SQL & PL/SQL » Join Query Optimisation
Join Query Optimisation [message #37408] Wed, 06 February 2002 06:12 Go to next message
Nick Heppleston
Messages: 3
Registered: February 2002
Junior Member
All, i have the following query which takes 12 minutes to execute - not being the world's greatest SQL programmer, i would appreciate your feedback as to how to optimise this query.

SELECT
TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MM:SS') || '^' ||
rtrim(ndm_name) || '^UK^' ||
rtrim(ndm_addr1) || '^' ||
rtrim(ndm_addr2) || '^' ||
rtrim(ndm_addr3) || '^' ||
rtrim(ndm_postcode) || '^' ||
rtrim(ndm_addr4) || '^' ||
rtrim(ndm_addr5) || '^' ||
rtrim(cmp_lkspcode) || '^' ||
rtrim(cmp_product) || '^' ||
rtrim(cmp_desc) || '^' ||
rtrim(soi_itqty) || '^' ||
rtrim(soh_currency)
FROM midw.soitem, midw.cmprod, midw.sohead, midw.dlcust, midw.ndmas
WHERE
soh_orddate = TO_CHAR(sysdate, 'DD fmMonth YYYY')
AND soi_stloc = 'SALT'
AND soi_ordref = soh_ordref (+)
AND cmp_product = soi_product (+)
AND soh_account = dlcus_customer (+)
AND dlcus_ndcode = ndm_ndcode (+)
AND cmp_usage = '077';

Any help greatly appreciated - where do i start to look at optimising the query, pointers, tips anything!!

Cheers, Nick
Re: Join Query Optimisation [message #37409 is a reply to message #37408] Wed, 06 February 2002 06:21 Go to previous message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
hi,

have you got right index ?
see the explain plan and find out the table access.

cheers
pratap
Previous Topic: ORA- 205 Can not mount database per.ora8 in win98
Next Topic: The Long datatype and Oracle7 string manipulations
Goto Forum:
  


Current Time: Sun Sep 19 00:20:43 CDT 2021