1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
| SQL> set timing on
SQL> set autot trace exp stat
SQL>
SQL> SELECT COUNT(1) FROM
2 (
3 With
4 FIRSTSTEP as (
5 SELECT DISTINCT G_T1.PARTNER_ID ,
6 G_T1.TRANSPORT_NO ,
7 G_T1.SHIPMENT_NO ,
8 G_T1.CD_EVENT_DELAY ,
9 G_T1.CD_FAULT_DELAY ,
10 G_T0.CD_EVENT
11 FROM DWH."TRACK_EVENT" G_T0,
12 DWH."SHIPMENT" G_T1
13 WHERE
14 ( G_T1.PARTNER_ID = G_T0.PARTNER_ID AND G_T1.TRANSPORT_NO = G_T0.TRANSPORT_NO )
15 AND G_T1.PARTNER_ID IN (130, 290)
16 AND G_T1.INBOUND_REF_DATE >= '15/02/2006'
17 AND G_T1.INBOUND_REF_DATE <= '23/02/2006'
18 AND G_T1.OUTBOUND_REF_DATE >= '15/02/2006'
19 AND G_T1.OUTBOUND_REF_DATE <= '23/02/2006'
20 AND G_T0.DT_MVT >= '15/02/2006'
21 AND G_T0.DT_MVT <= '23/02/2006'
22 AND G_T1.CANCELED = 0
23 AND G_T1.DELIVERED = 'EC'
24 AND G_T1.CD_FAULT_DELAY IS NULL
25 AND G_T1.CD_EVENT_DELAY IS NULL
26 )
27 SELECT
28 FIRSTSTEP.PARTNER_ID
29 , FIRSTSTEP.TRANSPORT_NO
30 , FIRSTSTEP.SHIPMENT_NO
31 , FIRSTSTEP.CD_EVENT_DELAY
32 , FIRSTSTEP.CD_FAULT_DELAY
33 , FIRSTSTEP.CD_EVENT
34 , TRACK_EVENT_TYPE.T_CLOCK_STOPPER
35 FROM
36 FIRSTSTEP
37 , DWH.TRACK_EVENT_TYPE
38 WHERE
39 FIRSTSTEP.PARTNER_ID = DWH.TRACK_EVENT_TYPE.PARTNER_ID
40 AND FIRSTSTEP.CD_EVENT = DWH.TRACK_EVENT_TYPE.CD_TE
41 AND DWH.TRACK_EVENT_TYPE.RESPONSABILITE = 'C'
42 );
EcoulÚ : 00 :05 :46.01
Plan d'exÚcution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1 Bytes=14)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=11 Card=1 Bytes=14)
3 2 VIEW (Cost=11 Card=1 Bytes=103)
4 3 SORT (UNIQUE) (Cost=11 Card=1 Bytes=129)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EVENT' (Co
st=2 Card=1 Bytes=27)
6 5 NESTED LOOPS (Cost=4 Card=1 Bytes=129)
7 6 NESTED LOOPS (Cost=3 Card=1 Bytes=102)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'SHIPMENT'
(Cost=2 Card=1 Bytes=62)
9 8 INDEX (RANGE SCAN) OF 'I_SHIPMENT_DELIVERE
D_OUTBOUND' (NON-UNIQUE) (Cost=4 Card=4)
10 7 INLIST ITERATOR
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'TRACK_EV
ENT_TYPE' (Cost=2 Card=1 Bytes=40)
12 11 INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_TYP
E_CD_TE' (UNIQUE) (Cost=1 Card=2)
13 6 INLIST ITERATOR
14 13 INDEX (RANGE SCAN) OF 'I_TRACK_EVENT_EVENTS'
(NON-UNIQUE) (Cost=1 Card=1)
Statistiques
----------------------------------------------------------
22 recursive calls
0 db block gets
57894112 consistent gets
2 physical reads
0 redo size
198 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed |
Partager