Hi Listers,
I have the below query which is taking almost 3 min. to execute.
Can anybody help me in rewriting the query and/or reducing the query
execution time. Please note that V_SHIP is a view created the source for
which is as as follows:
View Source
CREATE OR REPLACE VIEW V_SHIP ( SHIPMENT_ID,
HOUSEBILL, AEI_REFERENCE, CONTAINER_ID, CONTAINER_NUM,
CONTAINER_SIZE ) AS select
s.shipment_id,s.housebill,s.aei_reference,sc.container_id,sc.container_num,s
c.container_size
from shipment s,shipment_container sc
where s.shipment_id = sc.shipment_id(+)
and trunc(S.MSG_CREATION_DATE) >= trunc(sysdate - 90)
and trunc(S.MSG_CREATION_DATE) <= trunc(sysdate);
Query:
SELECT /*+ORDERED*/
S.HOUSEBILL,
OH.ORDER_REF_NO,
v.CONTAINER_NUM,
S.AEI_REFERENCE,
CE1.SEQUENCE,
TL1.COMPONENT_VALUE,
TL2.COMPONENT_VALUE,
(
SELECT
to_char(SCT1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_CONTAINER_TIMESTAMP SCT1
WHERE
SCT1.EVENT_ID = XM1.EVENT_ID
AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
AND SCT1.CONTAINER_ID = v.CONTAINER_ID
AND XM1.TABLE_NAME = 'SHIPMENT_CONTAINER_TIMESTAMP'
UNION
SELECT
to_char(ST1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_TIMESTAMP ST1
WHERE
ST1.EVENT_ID = XM1.ALT_EVENT_ID
AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
AND XM1.ALT_EVENT_ID IS NOT NULL
AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
AND NOT EXISTS (
SELECT
SCT1.TIMESTAMP
FROM
SHIPMENT_CONTAINER_TIMESTAMP SCT1
WHERE
SCT1.EVENT_ID = XM1.EVENT_ID
AND SCT1.SHIPMENT_ID = S.SHIPMENT_ID
AND SCT1.CONTAINER_ID = v.CONTAINER_ID
AND XM1.TABLE_NAME ='SHIPMENT_CONTAINER_TIMESTAMP'
)
UNION
SELECT
to_char(ST1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
SHIPMENT_TIMESTAMP ST1
WHERE
ST1.EVENT_ID = XM1.EVENT_ID
AND ST1.SHIPMENT_ID = S.SHIPMENT_ID
AND XM1.TABLE_NAME = 'SHIPMENT_TIMESTAMP'
UNION
SELECT
to_char(OT1.TIMESTAMP,
'YYYYMMDDHH24MISS')
FROM
ORDER_TIMESTAMP OT1
WHERE
OT1.EVENT_ID = XM1.EVENT_ID
AND OT1.SHIPMENT_ORDER_ID = SO.SHIPMENT_ORDER_ID
AND XM1.TABLE_NAME = 'ORDER_TIMESTAMP'
) AS FROM_DATE,
S.SHIPMENT_ID,
OH.ORDER_ID,
v.CONTAINER_ID
FROM
SHIPMENT_CUSTOMER SC,
V_SHIP v,
ORDER_HEADER OH,
SHIPMENT_ORDER SO,
SHIPMENT S,
TRAFFIC_LANE TL1,
TRAFFIC_LANE TL2,
CUSTOMER_EVENT CE1,
XM_FIELD XM1
WHERE
CE1.CUSTOMER_MOT_ID = 1040130842248
AND SC.CUSTOMER_MOT_ID = CE1.CUSTOMER_MOT_ID
AND V.SHIPMENT_ID = S.SHIPMENT_ID
AND s.SHIPMENT_ID = SO.SHIPMENT_ID(+)
AND V.SHIPMENT_ID = SC.SHIPMENT_ID
AND SO.ORDER_ID = OH.ORDER_ID(+)
and XM1.FIELD_ID = CE1.FIELD_ID
AND TL1.LANE_ID = (
SELECT
min(LANE_ID)
FROM
LANE
WHERE
CUSTOMER_MOT_ID = 1040130842248
)
AND TL1.TRAFFIC_LANE_NO = TL2.TRAFFIC_LANE_NO
AND TL1.LANE_ID < TL2.LANE_ID
AND TL2.COMPONENT_VALUE = S.ORG_STATION_CODE
AND TL1.COMPONENT_VALUE = S.DEST_STATION_CODE
ORDER BY
S.SHIPMENT_ID,
OH.ORDER_ID,
v.CONTAINER_ID,
CE1.SEQUENCE
Execution plan
Operation Object Name Rows Bytes Cost TQ In/Out
PStart PStop
SELECT STATEMENT Hint=CHOOSE 275 25352
SORT ORDER BY 275 84 K 25352
HASH JOIN 275 84 K 25337
MERGE JOIN 48 12 K 25332
HASH JOIN 150 35 K 25329
TABLE ACCESS FULL TRAFFIC_LANE 409 15 K 1
HASH JOIN 73 K 14 M 24668
TABLE ACCESS BY INDEX ROWID TRAFFIC_LANE 1 38 1
INDEX RANGE SCAN TL_LANE_ID_VALUE 1 2
SORT AGGREGATE 1 16
FIRST ROW 2 32 1
INDEX RANGE SCAN (MIN/MAX) LANE_CMOT_LANE_ID 2
1
HASH JOIN OUTER 7 M 1G 6564
HASH JOIN OUTER 198 K 30 M 2534
NESTED LOOPS 198 K 22 M 504
HASH JOIN OUTER 490 39 K 14
HASH JOIN 40 1 K 9
TABLE ACCESS BY INDEX ROWID SHIPMENT_CUSTOMER
39 1014 2
INDEX RANGE SCAN SHIP_CUS_CMOT_ID 39
1
TABLE ACCESS BY INDEX ROWID SHIPMENT 102
2 K 6
INDEX RANGE SCAN SHIP_MSG_CREATION_DT 102
2
TABLE ACCESS FULL SHIPMENT_CONTAINER 1 K 41 K
3
TABLE ACCESS BY INDEX ROWID SHIPMENT 40 K 1 M
1
INDEX UNIQUE SCAN XPKSHIPMENT 40 K
TABLE ACCESS FULL SHIPMENT_ORDER 82 3 K 1
TABLE ACCESS FULL ORDER_HEADER 3 K 36 K 7
SORT JOIN 32 416 3
TABLE ACCESS FULL CUSTOMER_EVENT 32 416 1
TABLE ACCESS FULL XM_FIELD 572 32 K 2
Any help in this regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
