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 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180
| SQL> SELECT
2 VEHICULE.PARC,
3 T_AFFECT4.CLIENT,
4 T_AFFECT2.FILIALE,
5 sum(DONNEE_TX.DISTANCE),
6 DONNEE_TX.CONSO_TOT_AVG,
7 sum(DONNEE_TX.CONSO_TOTAL),
8 DONNEE_TX.DATEJ,
9 to_char(DONNEE_TX.DATEJ,'WW'),
10 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME),
11 ETABSECT.LIB,
12 SOCIETE_PAND.LIB_SOC,
13 DONNEE_TX.ID_CONDUCTEUR,
14 DONNEE_TX.SPEED_AVG,
15 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30
39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40
49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end,
16 sum(DONNEE_TX.CONSO_IDLE),
17 sum(DONNEE_TX.CONSO_COND),
18 MARQUE.LIBELLE,
19 CATVEHIC.LIBELLE,
20 decode(INDIVIDU.SEXE,'F','Femme','Homme'),
21 to_char(DONNEE_TX.DATEJ,'YYYYMM'),
22 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2)
23 FROM
24 VEHICULE,
25 t_AFFECT4,
26 t_AFFECT2,
27 DONNEE_TX,
28 TR_TRA_PERSON,
29 INDIVIDU,
30 ETABSECT,
31 SOCIETE_PAND,
32 MARQUE,
33 CATVEHIC,
34 VEHICULE_AFFECTATION,
35 INFSAL
36 WHERE
37 ( VEHICULE_AFFECTATION.ID_VEHICULE=DONNEE_TX.PARCID AND VEHICULE_AFFECTATION.DATE_DEBUT<=DONNEE_TX.DATEJ AND VEHICULE_AFFECTATION.DATE_FIN>DONNEE_TX.DATEJ )
38 AND ( VEHICULE_AFFECTATION.ID_CLIENT=T_AFFECT4.ID_AFFECT4 )
39 AND ( VEHICULE_AFFECTATION.ID_FILIALE=T_AFFECT2.ID_AFFECT2 )
40 AND ( DONNEE_TX.PARCID=VEHICULE.VEHICULE_ID )
41 AND ( DONNEE_TX.ID_CONDUCTEUR=TR_TRA_PERSON.CNR_EXTCODE )
42 AND ( VEHICULE.COD_CATEGORIE=CATVEHIC.COD_CATEGORIE )
43 AND ( VEHICULE.MARQUE=MARQUE.COD_MARQUE )
44 AND ( INFSAL.NOBUL(+)=1 AND( DONNEE_TX.id_conducteur=to_char(INFSAL.individu(+)) AND to_number(to_char(DONNEE_TX.datej,'YYYYMM'))= case when to_char(sysdate,'DD')<='09' then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),2),'YYYYMM')) when to_char(DONNEE_TX.datej,'YYYYMM')=to_char(sysdate,'YYYYMM') then to_number(to_char(add_months(to_date(INFSAL.anmois(+),'YYYYMM'),1),'YYYYMM')) else to_number(INFSAL.anmois(+)) end ) )
45 AND ( to_char(INDIVIDU.INDIVIDU(+))=DONNEE_TX.ID_CONDUCTEUR )
46 AND ( INFSAL.SOCIETE=ETABSECT.SOCIETE(+) AND INFSAL.ETAB=ETABSECT.ETAB(+) AND INFSAL.SECTION=ETABSECT.SECTION(+) )
47 AND ( INFSAL.SOCIETE=SOCIETE_PAND.SOCIETE(+) )
48 AND
49 (
50 (
51 SOCIETE_PAND.LIB_SOC = 'GT BRETAGNE'
52 OR
53 T_AFFECT2.FILIALE = 'BRETAGNE'
54 )
55 AND
56 DONNEE_TX.CONSO_TOT_AVG > 0
57 AND
58 DONNEE_TX.IND_CONF = '0'
59 AND
60 to_char(DONNEE_TX.DATEJ,'YYYYMM') = '201209'
61 )
62 GROUP BY
63 VEHICULE.PARC,
64 T_AFFECT4.CLIENT,
65 T_AFFECT2.FILIALE,
66 DONNEE_TX.CONSO_TOT_AVG,
67 DONNEE_TX.DATEJ,
68 to_char(DONNEE_TX.DATEJ,'WW'),
69 nvl(INDIVIDU.NOM, TR_TRA_PERSON.NM_NAME),
70 ETABSECT.LIB,
71 SOCIETE_PAND.LIB_SOC,
72 DONNEE_TX.ID_CONDUCTEUR,
73 DONNEE_TX.SPEED_AVG,
74 case when (MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12)<30 then '1- Moins de 30 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 30 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 40 then '2- De 30
39 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 40 AND MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 < 50 then '3- De 40
49 Ans' when MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12 >= 50 then '4- Plus de 50 ans' end,
75 MARQUE.LIBELLE,
76 CATVEHIC.LIBELLE,
77 decode(INDIVIDU.SEXE,'F','Femme','Homme'),
78 to_char(DONNEE_TX.DATEJ,'YYYYMM'),
79 round(MONTHS_BETWEEN(sysdate,to_date(INDIVIDU.dnai,'YYYYMMDD'))/12,2)
80 HAVING
81 sum(DONNEE_TX.DISTANCE) > 0;
1009 ligne(s) sélectionnée(s).
Plan d'exécution
----------------------------------------------------------
Plan hash value: 487837382
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53 | 30210 | 4124 (2)| 00:00:50 | | |
|* 1 | FILTER | | | | | | | |
| 2 | HASH GROUP BY | | 53 | 30210 | 4124 (2)| 00:00:50 | | |
|* 3 | HASH JOIN OUTER | | 1049 | 583K| 4123 (2)| 00:00:50 | | |
|* 4 | HASH JOIN | | 23 | 12236 | 4025 (2)| 00:00:49 | | |
|* 5 | HASH JOIN | | 23 | 11822 | 4020 (2)| 00:00:49 | | |
| 6 | TABLE ACCESS FULL | T_AFFECT2 | 37 | 592 | 3 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 629 | 305K| 4016 (2)| 00:00:49 | | |
| 8 | NESTED LOOPS OUTER | | 296 | 136K| 3978 (2)| 00:00:48 | | |
|* 9 | HASH JOIN RIGHT OUTER | | 296 | 126K| 3976 (2)| 00:00:48 | | |
| 10 | TABLE ACCESS FULL | SOCIETE_PAND | 26 | 7254 | 3 (0)| 00:00:01 | | |
|* 11 | HASH JOIN OUTER | | 296 | 46768 | 3973 (2)| 00:00:48 | | |
|* 12 | HASH JOIN | | 296 | 32264 | 350 (2)| 00:00:05 | | |
| 13 | TABLE ACCESS FULL | CATVEHIC | 196 | 4704 | 3 (0)| 00:00:01 | | |
|* 14 | HASH JOIN | | 296 | 25160 | 346 (2)| 00:00:05 | | |
| 15 | TABLE ACCESS FULL | MARQUE | 154 | 1694 | 3 (0)| 00:00:01 | | |
|* 16 | HASH JOIN | | 296 | 21904 | 343 (2)| 00:00:05 | | |
|* 17 | HASH JOIN | | 337 | 19546 | 287 (2)| 00:00:04 | | |
|* 18 | TABLE ACCESS FULL| DONNEE_TX | 336 | 14784 | 274 (1)| 00:00:04 | | |
|* 19 | TABLE ACCESS FULL| TR_TRA_PERSON | 854 | 11956 | 12 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS FULL | VEHICULE | 4116 | 65856 | 56 (2)| 00:00:01 | | |
| 21 | REMOTE | INFSAL | 9578 | 458K| 3622 (2)| 00:00:44 | MERCU~ | R->S |
| 22 | REMOTE | ETABSECT | 1 | 35 | 1 (0)| 00:00:01 | MERCU~ | R->S |
| 23 | TABLE ACCESS FULL | VEHICULE_AFFECTATION | 28064 | 712K| 38 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS FULL | T_AFFECT4 | 526 | 9468 | 5 (0)| 00:00:01 | | |
| 25 | REMOTE | INDIVIDU | 25491 | 945K| 98 (2)| 00:00:02 | MERCU~ | R->S |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("DONNEE_TX"."DISTANCE")>0)
3 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INDIVIDU"."INDIVIDU"(+)))
4 - access("VEHICULE_AFFECTATION"."ID_CLIENT"="T_AFFECT4"."ID_AFFECT4")
5 - access("VEHICULE_AFFECTATION"."ID_FILIALE"="T_AFFECT2"."ID_AFFECT2")
filter("SOCIETE_PAND"."LIB_SOC"='GT BRETAGNE' OR "T_AFFECT2"."FILIALE"='BRETAGNE')
7 - access("VEHICULE_AFFECTATION"."ID_VEHICULE"="DONNEE_TX"."PARCID")
filter("VEHICULE_AFFECTATION"."DATE_DEBUT"<="DONNEE_TX"."DATEJ" AND
"VEHICULE_AFFECTATION"."DATE_FIN">"DONNEE_TX"."DATEJ")
9 - access("INFSAL"."SOCIETE"="SOCIETE_PAND"."SOCIETE"(+))
11 - access("DONNEE_TX"."ID_CONDUCTEUR"=TO_CHAR("INFSAL"."INDIVIDU"(+)))
filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM'))=CASE WHEN
(TO_CHAR(SYSDATE@!,'DD')<='09') THEN TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS"(+),'YYYYMM'),2),'YY
YYMM')) WHEN (TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')=TO_CHAR(SYSDATE@!,'YYYYMM')) THEN
TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE("INFSAL"."ANMOIS"(+),'YYYYMM'),1),'YYYYMM')) ELSE
TO_NUMBER("INFSAL"."ANMOIS"(+)) END )
12 - access("VEHICULE"."COD_CATEGORIE"="CATVEHIC"."COD_CATEGORIE")
14 - access("VEHICULE"."MARQUE"="MARQUE"."COD_MARQUE")
16 - access("DONNEE_TX"."PARCID"="VEHICULE"."VEHICULE_ID")
17 - access("DONNEE_TX"."ID_CONDUCTEUR"="TR_TRA_PERSON"."CNR_EXTCODE")
18 - filter("DONNEE_TX"."IND_CONF"='0' AND TO_CHAR(INTERNAL_FUNCTION("DONNEE_TX"."DATEJ"),'YYYYMM')='201209'
AND "DONNEE_TX"."CONSO_TOT_AVG">0)
19 - filter("TR_TRA_PERSON"."CNR_EXTCODE" IS NOT NULL)
20 - filter("VEHICULE"."VEHICULE_ID" IS NOT NULL AND "VEHICULE"."MARQUE" IS NOT NULL AND
"VEHICULE"."COD_CATEGORIE" IS NOT NULL)
Remote SQL Information (identified by operation id):
----------------------------------------------------
21 - SELECT "ANMOIS","NOBUL","SOCIETE","INDIVIDU","ETAB","SECTION" FROM "PANDORE"."INFSAL" "INFSAL" WHERE
"NOBUL"=1 (accessing 'MERCURE.GT' )
22 - SELECT "SOCIETE","ETAB","SECTION","LIB" FROM "PANDORE"."ETABSECT" "ETABSECT" WHERE :1="SOCIETE" AND
:2="ETAB" AND :3="SECTION" (accessing 'MERCURE.GT' )
25 - SELECT "INDIVIDU","NOM","SEXE","DNAI" FROM "PANDORE"."INDIVIDU" "INDIVIDU" (accessing 'MERCURE.GT' )
Note
-----
- dynamic sampling used for this statement (level=2)
Statistiques
----------------------------------------------------------
4 recursive calls
1 db block gets
1393 consistent gets
1000 physical reads
256 redo size
86937 bytes sent via SQL*Net to client
1153 bytes received via SQL*Net from client
69 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1009 rows processed
SQL> spool off; |
Partager