| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query tuning help needed
I agree with you Raj - if you're saying that the in-line view is the new
feature. I recently inherited an incredibly complex SQL statement with
several in-line views that took over 2 hours to run, the optimiser simply
couldn't cope.
I took the old-fashioned route and broke the statement up by creating temporary tables and replacing the in-line views with these temporary tables. Even with creating the tables the whole thing now runs in about 15 mins.
Furthermore it took me hours to work out what the damn SQL was doing before I could rewrite it. Stick to the KISS methodology is my advice.
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 29/04/03 17:56
Brad, Ranga
I am sorry but I have to disagree, stored procedure is not an answer to
this problem. I prefer to call this
I-saw-a-new-feature-and-i-want-to-use-it syndrome.
How about following ...
SELECT ORIGIN
,DESTINATION
,SERVICE_CODE
,CASE WHEN SERVICE_CODE = 'X' THEN AVG(SEG_TIME) + 1
WHEN SERVICE_CODE = 'Y' THEN AVG(SEG_TIME) + 2
WHEN SERVICE_CODE = 'Z' THEN AVG(SEG_TIME) + 3
WHEN SERVICE_CODE = 'Q' THEN AVG(SEG_TIME) + 4 END AS
ADD_ON_VALUE
,SYSDATE
,'SYSTEM'
FROM
(SELECT META_ROUTING_KEY AS ROUTE_KEY
,ST1.STATION_CODE AS ORIGIN
,ST2.STATION_CODE AS DESTINATION
,SERVICE_CODE
,SUM(CASE WHEN t.TYPE IN ('OD','OT','TD','TT') THEN
NVL(TT_TIME_IN_MIN,0) seg_time
FROM SEG_MIN_TIMES M, STATION ST3, STATION
ST4,PRY_TWO_MR_TRANSIT T, SERVICE
WHERE M.ORIGIN = ST3.STATION_CODE
AND M.DESTINATION = ST4.STATION_CODE
AND ST3.STN_KEY = T.SEG_ORG_STN_KEY
AND ST4.STN_KEY = T.SEG_DEST_STN_KEY
AND M.SERVICE = SERVICE_CODE
AND SERVICE_CODE IN ('X','Y','Z','Q')
GROUP BY META_ROUTING_KEY, ST1.STATION_CODE,
ST2.STATION_CODE,SERVICE_CODE
)
Test and Test and Test ... this should give you an idea though ... Raj
<<ESPN_Disclaimer.txt>>
###########################################
This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.com/
This communication and the information it contains: - (a) Is intended for the person(s) or organisation(s) named above and for no other person(s) or organisation(s). Access to this mail by anyone else is unauthorised. (b) Is confidential, and may be legally privileged or otherwise protected in law. Unauthorised use, circulation, copying or disclosure of any part of this communication may be unlawful. (c) May be susceptible to interference, and should not be assumed that it has come in its original form and/or from the stated sender or PinkRoccade UK accepts no responsibility for information, errors or omissions in this e-mail or use or misuse thereof or any act done or omitted to be done in connection with this communication. If you are not the intended recipient, please inform postmaster_at_pinkroccade.co.uk immediately and delete it and all copies from your system.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Morrin, Liam INET: Liam.Morrin_at_pinkroccade.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Apr 29 2003 - 13:06:46 CDT
 |
 |