Optimisation d'un script

Version imprimable

28/04/2018, 13h18
lotofski

1 pièce(s) jointe(s)

Optimisation d'un script

Est ce que qlq'un peut m'aider a optimisé un scripte SQL (il fonctionne mtn normalement ? svp Pièce jointe 373874 )
merci d'avance

Bonjour,
Pour répondre correctement à ta question il faudrait

avoir un descriptif de tes tables
avoir un plan d'execution réel pour savoir où ça coince
avoir la liste des index
savoir ton sgbd

Sinon à la lecture de ton script j'ai vu plusieurs fois dans un order by de fonction de fenêtrage

Code:

case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end

Cela pourrait se transformer en une table de correspondance ou une vue
Code:

1 2 3 4 5 6 7 select '74' as Code_EVT, '1000' as Ordre union select '43' as Code_EVT, '1000' as Ordre union select '21' as Code_EVT, '0' as Ordre union select '42' as Code_EVT, '0' as Ordre
Tu fais une jointure externe sur cette table de correspondance et ton order by devient

Code:

COALESCE(ORDRE,CODE_EVT)

Par ailleurs
Code:

1 2 3 4 5 6 7 8 9 10 ,CAST( SUBSTRING (CAST ( TO_TIMESTAMP( GREATEST( COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'), COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000') ),'yyyymmddhh24miss') AS VARCHAR(25)) FROM 1 FOR 19) AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS') AS DAT_EVT_UTC
me semble quelque peu compliqué, et n'utilisera jamais d'index que cherches tu à faire?
Enfin si tu ne veux pas prendre le risque d'être spammé enlève ton adresse mail de ton post.
Cordialement
Soazig

29/04/2018, 13h10
lotofski

le scripte fonctionne bien mais on dois l'optimiser , on travaille sur teradata
29/04/2018, 13h44
soazig

Bonjour,
Tu n'as répondu qu'à une question sur 5.
Je reformule pour le plan d’exécution il servira à savoir ce qui prend du temps, et quelle est ou quelles sont la ou les portions à remanier.

As tu essayé ma suggestion avec table de correspondance.
Nous ne sommes pas magiciens, sans information nous ne pouvons pas deviner.
A+
Soazig
29/04/2018, 14h54
lotofski

Merci pour votre réponse , pour le reste des questions je peux réponde demain en rentrant au boulot , j'ai ramené que le script pour le moment , merci encore une fois .
29/04/2018, 15h06
lotofski

voila le message de demande d'optimisation ''Optimisation technique de la requête sur le calcul d'EVENT.
Avec la quantité croissante d'événement, une optimisation est nécessaire pour éviter tout accident dans un proche avenir.''
30/04/2018, 09h42
lotofski

4 pièce(s) jointe(s)

la création de la table , index

Bonjour , soazik , voici les informations demandées , merci pour votre aide .Pièce jointe 374201 Pièce jointe 374203 Pièce jointe 374207 Pièce jointe 374211
30/04/2018, 10h22
soazig

Bonjour,
Tu nous as fourni le script d'une table et pas des 3 tables utilisées. On n'a pas de script d'index , du coup on ne sait pas s'il n'y en a pas, ou si tu ne les as pas fourni.
Cordialement
Soazig
30/04/2018, 10h40
escartefigue
Bonjour,

Avant de parler d'optimisation, il faudrait déjà que ce script fonctionne, je doute que ce soit le cas au vu des premières lignes fournies

Par exemple l'extrait ci-dessous est erroné
Code:

1 2 ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'
Exécutez le SELECTseul dans un premier temps pour vérifier son bon fonctionnement (et de préférence par petits bouts)

Ensuite, vu la taille conséquente de la requête, il serait préférable d'exprimer le besoin fonctionnel (que cherchez vous à obtenir) et communiquer le MLD pour voir si une autre approche plus simple ne serait pas plus efficiente ;)
30/04/2018, 11h20
lotofski

Bonjour escartefigue , merci pour votre réponse , le script marche trés bien , il faut juste commencer par le bas l’exécution .

4 pièce(s) jointe(s)

Bonjour soazig
Voici les 4 tables Pièce jointe 374263 Pièce jointe 374264 Pièce jointe 374267 Pièce jointe 374268

Code:

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_CNT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code UPU' NOT NULL COMPRESS '001',
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' NOT NULL COMPRESS '0',
      DAT_EVT_UTC TIMESTAMP(0) TITLE 'Event timestamp in UTC' NOT NULL,
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event timestamp in Local' COMPRESS ,
      COD_EVT_MSG CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code from the message' COMPRESS ,
      NOM_SRC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source' COMPRESS ,
      IDT_COD_CNT VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container Instance ID' COMPRESS ,
      STT_LVL_SCA CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Scan Level' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross Weight (Kg)' COMPRESS ,
      COD_RSN CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Reason Code' COMPRESS ,
      COD_CIE_HDL VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Handling Transport Company' COMPRESS ,
      LCT_SCA VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Scan Location' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      DAT_CRN_EVT TIMESTAMP(0) TITLE 'Event Timestamp written to disk' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_CNT ( IDT_CNT_RCU ,DAT_EVT_UTC );

Code:

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_RCU ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event time stamp Local Time' NOT NULL,
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code' NOT NULL COMPRESS '001',
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      SRC_DON_ESC CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Data sourced from Station' NOT NULL COMPRESS ('AMS','CDG','CPH','JFK'),
      SRC_ESC_TSP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source Station Transport Co.' NOT NULL COMPRESS '2',
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' COMPRESS ,
      IDT_CNT VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container ID' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross weight' COMPRESS ,
      COD_RSN CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ReasonCode' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      COD_LCT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Location' COMPRESS ,
      NOM_CND VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Driver' COMPRESS ,
      STT_SPP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delete Flag' COMPRESS ,
      STT_MLD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MLD generated flag' COMPRESS ,
      STT_ANL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD' COMPRESS ,
      STT_ANL_2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD 2' COMPRESS ,
      STT_LIV CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivery confirmed flag' COMPRESS ,
      DAT_LIV TIMESTAMP(0) TITLE 'Delivery Timestamp' COMPRESS ,
      DAT_DNI_MDF VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivered to Office of Exchange' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      STT_MDF_PC CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed on Workstation' COMPRESS ,
      NUM_VOL_DEP_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      CIE_VOL_DEP CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      CIE_VOL_ARV CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_RCU ( IDT_CNT_RCU ,DAT_LOC_EVT );
 
 
SELECT	IDT_CNT_RCU, DAT_LOC_EVT, COD_EVT, DAT_CRN_ODS, SRC_DON_ESC,
		SRC_ESC_TSP,
		IDT_TYP,
		IDT_CNT,
		PDS_BRT,
		COD_RSN,
		ESC_ORI,
		ESC_DST,
		NOM_PRN_ORI,
		NOM_PRN_DST,
		NUM_VOL_DEP,
		NUM_VOL_ARV,
		DAT_DEP,
		DAT_VOL,
		IDT_VOL,
		COD_LCT,
		NOM_CND,
		STT_SPP,
		STT_MLD,
		STT_ANL,
		STT_ANL_2,
		STT_LIV,
		DAT_LIV,
		DAT_DNI_MDF,
		DAT_MDF,
		UTI_MDF,
		STT_MDF_PC,
		NUM_VOL_DEP_CLC,
		NUM_VOL_ARV_CLC,
		CIE_VOL_DEP,
		CIE_VOL_ARV,
		DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_RCU

Code:

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_RCU ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event time stamp Local Time' NOT NULL,
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code' NOT NULL COMPRESS '001',
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      SRC_DON_ESC CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Data sourced from Station' NOT NULL COMPRESS ('AMS','CDG','CPH','JFK'),
      SRC_ESC_TSP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source Station Transport Co.' NOT NULL COMPRESS '2',
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' COMPRESS ,
      IDT_CNT VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container ID' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross weight' COMPRESS ,
      COD_RSN CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ReasonCode' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      COD_LCT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Location' COMPRESS ,
      NOM_CND VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Driver' COMPRESS ,
      STT_SPP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delete Flag' COMPRESS ,
      STT_MLD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MLD generated flag' COMPRESS ,
      STT_ANL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD' COMPRESS ,
      STT_ANL_2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD 2' COMPRESS ,
      STT_LIV CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivery confirmed flag' COMPRESS ,
      DAT_LIV TIMESTAMP(0) TITLE 'Delivery Timestamp' COMPRESS ,
      DAT_DNI_MDF VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivered to Office of Exchange' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      STT_MDF_PC CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed on Workstation' COMPRESS ,
      NUM_VOL_DEP_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      CIE_VOL_DEP CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      CIE_VOL_ARV CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_RCU ( IDT_CNT_RCU ,DAT_LOC_EVT );
 
 
SELECT	IDT_CNT_RCU, DAT_LOC_EVT, COD_EVT, DAT_CRN_ODS, SRC_DON_ESC,
		SRC_ESC_TSP,
		IDT_TYP,
		IDT_CNT,
		PDS_BRT,
		COD_RSN,
		ESC_ORI,
		ESC_DST,
		NOM_PRN_ORI,
		NOM_PRN_DST,
		NUM_VOL_DEP,
		NUM_VOL_ARV,
		DAT_DEP,
		DAT_VOL,
		IDT_VOL,
		COD_LCT,
		NOM_CND,
		STT_SPP,
		STT_MLD,
		STT_ANL,
		STT_ANL_2,
		STT_LIV,
		DAT_LIV,
		DAT_DNI_MDF,
		DAT_MDF,
		UTI_MDF,
		STT_MDF_PC,
		NUM_VOL_DEP_CLC,
		NUM_VOL_ARV_CLC,
		CIE_VOL_DEP,
		CIE_VOL_ARV,
		DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_RCU

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_VOL_GRP ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_EVT CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ID evt' NOT NULL,
      IDT_VOL CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' NOT NULL,
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_VOL_GRP ( IDT_EVT ,IDT_VOL );
 
 
SELECT	IDT_EVT, IDT_VOL, DAT_CRN_ODS, DAT_MDF, DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_VOL_GRP

30/04/2018, 12h19
soazig

Il y a t-il des index sur ces tables?
30/04/2018, 12h25
escartefigue

Citation:

Envoyé par lotofski

Bonjour escartefigue , merci pour votre réponse , le script marche trés bien , il faut juste commencer par le bas l’exécution .

OK, il faut avouer que avec cette requête à rallonge, c'est très difficile à lire, d'autant que la mise en forme laisse à désirer :?

Sinon vous n'avez communiqué que les PRIMARY INDEX, s'il n'y a rien d'autre alors vos critères de jointure (IDT_COD_CNT) et de filtrage (IDT_TYP) ne sont pas indexés
30/04/2018, 12h37
Waldar

Il faut voir où le temps passe, si c'est du CPU, de la redistribution de données ou les deux.
Regardez dans DBQLogs ces informations si vous y avez accès, sinon demandez à un DBA de regarder pour vous.

Le script étant architecturé en blocs, c'est assez simple de créer plusieurs tables volatiles et de les remplir une à une au moins pour voir où le temps passe.

Les différentes fonctions de fenêtrage imposent des redistributions (coûteuses).

Le problème global que je vois avec ce script c'est que vous faites un traitement complet, il n'y a aucun filtre de date.
Potentiellement vous refaites le même boulot à chaque fois que vous l'exécutez sur un gros volume de données.

Sur quelle version de TD êtes-vous ?

Code:

select version from dbc.dbcinfo;

Avez-vous accès à l'option de partitionnement ?

Vous n'avez pas mis les bonnes tables dans vos descriptions, il manque les tables STAMP_OPS_EVT_TMP & DWHFRT.STAMP_CNT_RCU.
Vous avez donné deux fois la définition de la table DWHFRT.STAMP_EVT_RCU et une fois celle de DWHFRT.STAMP_EVT_VOL_GRP, mais elles ne font pas partie du script.

@soazig, non pas d'index hormis les PI, sinon ils seraient dans les scripts create.
30/04/2018, 13h03
lotofski

1 pièce(s) jointe(s)

Bonjour #waldar pour la qst (votre qst : il manque les tables STAMP_OPS_EVT_TMP & DWHFRT.STAMP_CNT_RCU.) TMP c'est temporaire pas de table pour ça voir piéce , Pièce jointe 374300 ( je viens d'arriver sur le projet il m'ont demandés de l'optimiser sachant que le script marche .)
voila le descriptif du ticket
"Technical optimisation of query about EVENT calculation.With the increasing amount of event, an optimisation is needed to avoid any crash in a near future.

(STAMP_OPS_EVT_TMP.bteq) file."
30/04/2018, 13h54
lotofski

5 pièce(s) jointe(s)

l'exécution du script

Pièce jointe 374312 Pièce jointe 374313 Pièce jointe 374317 Pièce jointe 374319 Pièce jointe 374323 on tout il me ramené 16039879 réel
30/04/2018, 14h12
lotofski

1 pièce(s) jointe(s)

exemple du résultat

Pièce jointe 374337 un extrait du résultat
30/04/2018, 15h02
Waldar

Qu'avez-vous suivi comme formation Teradata d'une part et SQL d'autre part ?

Parce que là, vous m'avez l'air assez débutant : ce n'est pas une tare on l'a tous été, mais les sujets d'optimisations demandent une connaissance (approfondie) du SQL, des mécanismes internes de la base de données, et avoir une petite couche de fonctionnel s'avère nécessaire dans ce cas présent.
30/04/2018, 15h08
lotofski

1 pièce(s) jointe(s)

bonjour ,
Oui je connais pas grand chose en teradata , et j'ai fait un peu sql et voici la table demandé Pièce jointe 374341

Bonjour,
Tu nous as fourni la description de beaucoup de table qui ne figurent pas dans ton script mais pas STAMP_CNT_RCU ou je l'ai raté.
Je propose que tu essaies progressivement de créer les index suivants

table DWHFRT.STAMP_CNT_RCU index sur IDT_COD_CNT
table DWHFRT.STAMP_EVT_CNT index sur IDT_COD_CNT
table DWHFRT.STAMP_EVT_CNT index sur IDT_TYP
table DWHFRT.STAMP_CNT_RCU index sur IDT_TYP

Tu crée un index tu testes, tu en crées un autre tu testes, etc..
Par ailleurs, j'ai modifié ton script en utilisant une correspondance, à tester après les index. J'ai aussi enlevé les colonnes NON_CORR_DAT_EVT_UTC, NON_CORR_DAT_EVT_CNT_UTC qui ne servaient pas dans le résultat final. Vu la taille de la requete il peut y avoir des coquilles.

Code:

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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
;With Correspondance
AS
(
select '74' as Code_EVT, '1000' as Ordre
union
select '43' as Code_EVT, '1000' as Ordre
union
select '21' as Code_EVT, '0' as Ordre
union 
select '42' as Code_EVT, '0' as Ordre
)
INSERT INTO STAMP_OPS_EVT_TMP
Select 
	--ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent
	NUM_RTE
	--On reporte les numéro de station qui ne changent pas.
    ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA 
    end as NUM_STA
	,COD_EVT
    ,IDT_CNT_RCU
    ,DAT_EVT_UTC
    ,DAT_LOC_EVT
    ,DAT_DEP
    ,LCT_SCA
	,ESC_ORI
    ,ESC_DST
    ,NOM_PRN_ORI
    ,NOM_PRN_DST
    ,NUM_VOL_DEP
    ,NUM_VOL_ARV
    ,IDT_VOL
 
 FROM 
	(
	--ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5)
	Select 
	NUM_RTE
 
		--S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station
		,case 
		when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Odre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
		else NUM_STA 
		end as 
		 NUM_STA
		,COD_EVT
		,IDT_CNT_RCU
		,DAT_EVT_UTC
		,DAT_LOC_EVT
		,DAT_DEP
		,LCT_SCA
		,ESC_ORI
		,ESC_DST
		,NOM_PRN_ORI
		,NOM_PRN_DST
		,NUM_VOL_DEP
		,NUM_VOL_ARV
		,IDT_VOL
		,Ordre
	 FROM 
		(
		-- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1'
		Select 
			NUM_RTE
			--indicateur de changement de station pour chaque route
			,case 
				when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), 0) <> LCT_SCA  then  1
				else null
				end  as NUM_STA
			,COD_EVT
			,IDT_CNT_RCU
			,DAT_EVT_UTC
			,DAT_LOC_EVT
			,DAT_DEP
			,LCT_SCA
			,ESC_ORI
			,ESC_DST
			,NOM_PRN_ORI
			,NOM_PRN_DST
			,NUM_VOL_DEP
			,NUM_VOL_ARV
			,IDT_VOL
			,Ordre
		FROM 
			(
			--ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent
				Select 
				case when NUM_RTE is null then 
											-- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur
											Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null
											then 1
											Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING)
											end
										else NUM_RTE end as NUM_RTE
				,COD_EVT
				,IDT_CNT_RCU
				,DAT_EVT_UTC
				,DAT_LOC_EVT
				,DAT_DEP
				,LCT_SCA
				,ESC_ORI
				,ESC_DST
				,NOM_PRN_ORI
				,NOM_PRN_DST
				,NUM_VOL_DEP
				,NUM_VOL_ARV
				,IDT_VOL
				,Ordre
			FROM 
				(
				--ETAPE TMP3 : On affecte un numéro croissant à chaque début de route
				SELECT distinct
					 --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route
					case 
					when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
					else RTE 
					end as NUM_RTE			   
					,TMP2.COD_EVT
					,TMP2.IDT_CNT_RCU
					,TMP2.DAT_EVT_UTC
					,TMP2.DAT_LOC_EVT
					,TMP2.DAT_DEP
					,TMP2.LCT_SCA
					,TMP2.ESC_ORI
					,TMP2.ESC_DST
					,TMP2.NOM_PRN_ORI
					,TMP2.NOM_PRN_DST
					,TMP2.NUM_VOL_DEP
					,TMP2.NUM_VOL_ARV
					,TMP2.IDT_VOL
					,Ordre
				FROM
				   ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' 
					SELECT
						--On affecte un indicateur  pour chaque début de route du Receptacle
						--s il s agit d un premier evènement (sauf iSC) sans début identifié
						case 
						when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU 
												order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  ) = 1 
								AND COD_EVT <>'iSC' 
								AND COD_EVT <>'6'
						then 1
						--s il s agit bien d un début identifié
						when COD_EVT in ('74','43') 
						then 1
						-- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même
						when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), '#') in ('21', '42')  
						then 1
						else null end as RTE
					   ,TMP1.*
					   FROM
 
 
						   (
							-- ETAPE TMP1: On reccupère les information de base
							SELECT 
								'RECEPTACLE' as LVL
								,COD_EVT
								,IDT_CNT_RCU
								--,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC
								,DAT_EVT_UTC
								,DAT_LOC_EVT
								,DAT_DEP
								,LCT_SCA
								,ESC_ORI
								,ESC_DST
								,NOM_PRN_ORI
								,NOM_PRN_DST
								,NUM_VOL_DEP
								,NUM_VOL_ARV
								,IDT_VOL
								,COALESCE(C.Ordre, EVT2.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT2
							left outer join Correspondance C on
							EVT2.COD_EVT=C.COD_EVT
							--Receptacle level
							WHERE IDT_TYP = 'R'
 
							UNION 
 
							SELECT 
								 'CONTAINER' as LVL
								 ,EVT.COD_EVT
								,CNT.IDT_CNT_RCU
								--DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD)
								--,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC
								--,Las plus grande des deux dates :(CNT.DAT_DEB_VLD  , EVT.DAT_EVT_UTC     ) as DAT_EVT_UTC
								,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC
								,EVT.DAT_LOC_EVT
								,EVT.DAT_DEP
								,EVT.LCT_SCA
								,EVT.ESC_ORI
								,EVT.ESC_DST
								,EVT.NOM_PRN_ORI
								,EVT.NOM_PRN_DST
								,EVT.NUM_VOL_DEP
								,EVT.NUM_VOL_ARV
								,EVT.IDT_VOL
								,COALESCE(C.Ordre, EVT.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT
 
							INNER JOIN DWHFRT.STAMP_CNT_RCU CNT
									ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT
							left outer join Correspondance C on
							EVT.COD_EVT=C.COD_EVT
							--Container level
							WHERE EVT.IDT_TYP = 'C'
							  AND CNT.IDT_TYP = 'R'		
							) as TMP1
							--where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054'
					) as TMP2
				) as TMP3
 
			) as TMP4
		) as TMP5
	) as TMP6
 --order by 4,5
;

Bon premier mai
Soazig

01/05/2018, 11h13
soazig

Rebonjour,
N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.
Par ailleurs, j'avoue mon ignorance quand aux indications

Code:

rows between 1 preceding and 1 preceding

A+
Soazig
01/05/2018, 11h53
lotofski

Bonjour SOAZIG ;
Merci pour votre aide , je vais suivre tes conseilles et voir aprés le résultat , merci beaucoup et bon 1 mai
01/05/2018, 16h31
lotofski

Rebonjour Soazig ,
Merci beaucoup pour votre aide , mais comme je peux pas l'essayer OJR , mais j'essaye de comprendre un peu le script :
- dans chaque sélect on trouve ordre , il sert à quoi ( je veux juste apprendre je connais pas grand chose )
- et pour les index que je dois créer , est ce que ça va pas créer des anomalies vu que ptr ils les ont déja créer .
Merci encore une fois pour votre aide , je dois l'essayer demain et l'expliquer pendant la réunion du matin :aie: :aie: :aie:
01/05/2018, 18h40
soazig
Rebonjour,
Pour ordre j'ai fait ce que je te suggérai à mon premier poste.
J'ai remplacé

Code:

case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end

par Ordre.
On a une table de correspondance nommé Correspondance qui est sous forme de with et qui indique
Code:

1 2 3 4 5 Cod_EVT;Ordre 74;1000 43:1000 21;0 42;0
Donc pour ce qui est dans la table de correspondance à la place du case when il suffit de prendre la colonne Ordre, pour ce qui n'y est pas on est sur le else du case when on met COD_EVT. C'est pour cela que la jointure sur Correspondance est externe, pour prendre en compte ce qui n'est pas dans la table de correspondance.
[Edit]Comme le case when apparaissait dans un order by je l'ai appelé ordre mais si fonctionnellement ça a un nom, il faut utiliser ce nom
[/edit]
Pour les index maintenant, je ne comprends pas ptr dans

Citation:

et pour les index que je dois créer , est ce que ça va pas créer des anomalies vu que ptr ils les ont déja créer .

Si l'index existe il ne faut pas le créer, je croyais qu'il n'y avait pas d'index sur tes tables à part la clé primaire.
Si tu crées des index non uniques cela ne créera pas d'anomalie, cela peut ralentir les écritures dans les tables concernées.
[Edit]Remarque on peut créer plusieurs index par tables sans problème, et c'est très fréquent[/Edit]
Cordialement
Soazig
02/05/2018, 00h03
lotofski

je vous remercie pour tout ces détailles , merci beaucoup
02/05/2018, 11h16
lotofski

2 pièce(s) jointe(s)

Bonjour Soazig ;
Voila j'ai lancé l script mais j'ai des erreurs ,avec et sans correspondance Pièce jointe 375291 Pièce jointe 375295 ,
et j'ai essayé de garder le mm script d'avant et de mettre les 3 lignes en commentaires pour voir si il ramènent ou pas (finalement ils ramènent perce-que 2000 lignes en plus )
02/05/2018, 11h36
aieeeuuuuu

Bonjour,

Citation:

Envoyé par soazig

N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.

Cela permet de limiter la fenêtre aux lignes qui précédent la ligne courante, selon l'ordre spécifié.
très pratique notamment avec un SUM pour obtenir une somme cumulée croissante.
02/05/2018, 11h53
soazig

Bonjour,
Merci aieeeuuuuu, j'aurais appris quelque chose.
Pour lotofski, et ses erreurs d'execution; peut-être que Teradata ne permet pas l'usage de CTE dans un insert, peux-tu essayer juste le select?
Que donne les index, parce que c'est surtout là que tu peux gagner?
Cordialement
Soazig
02/05/2018, 12h03
Waldar

Citation:

Envoyé par soazig

Code:

rows between 1 preceding and 1 preceding

C'est une clause des fonctions de fenêtrage qui permettent de borner leur utilisation à un nombre de lignes précis, ici de 1 en arrière jusqu'à 1 en arrière, on est pile dans l'équivalence de la fonction LAG(1).
Cette fonction n'a été implémentée qu'assez récemment dans Teradata, en v15 ou 16 je ne sais plus.

À noter qu'on peut aussi utiliser RANGE à la place de ROWS ce qui permet d'avoir des bornes dynamiques & plus fonctionnelles que techniques.
C'est déjà référencé dans cet article qui date de 2004 :
https://lalystar.developpez.com/fonc...alytiques/#LII
02/05/2018, 12h08
Waldar

Citation:

Envoyé par soazig

Pour lotofski, et ses erreurs d'execution; peut-être que Teradata ne permet pas l'usage de CTE dans un insert, peux-tu essayer juste le select ?

La syntaxe with insert select est propre à SQL-Server, et c'est une aberration d'avoir un ordre DML qui apparaît en plein milieu d'un SELECT.

Citation:

Envoyé par soazig

Que donne les index, parce que c'est surtout là que tu peux gagner?

Probablement la table a grossie et les index ne seront pas utilisés :)
Teradata étant un MPP les index, hors primaires, sont très très rarement utiles.

Bonjour,
Concernant la requête que j'ai proposée, tu peux remplacer le with Correspondance par une vrai table ou par une table temporaire.
L'autre solution mais qui devient lourde,c'est de remplacer l'utilisation de correspondance par sa définition comme ceci.
Mais déjà que la requête n'est pas très lisible, ça commence à être vraiment compliqué.

Code:

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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
INSERT INTO STAMP_OPS_EVT_TMP
Select 
	--ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent
	NUM_RTE
	--On reporte les numéro de station qui ne changent pas.
    ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA 
    end as NUM_STA
	,COD_EVT
    ,IDT_CNT_RCU
    ,DAT_EVT_UTC
    ,DAT_LOC_EVT
    ,DAT_DEP
    ,LCT_SCA
	,ESC_ORI
    ,ESC_DST
    ,NOM_PRN_ORI
    ,NOM_PRN_DST
    ,NUM_VOL_DEP
    ,NUM_VOL_ARV
    ,IDT_VOL
 
 FROM 
	(
	--ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5)
	Select 
	NUM_RTE
 
		--S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station
		,case 
		when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Odre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
		else NUM_STA 
		end as 
		 NUM_STA
		,COD_EVT
		,IDT_CNT_RCU
		,DAT_EVT_UTC
		,DAT_LOC_EVT
		,DAT_DEP
		,LCT_SCA
		,ESC_ORI
		,ESC_DST
		,NOM_PRN_ORI
		,NOM_PRN_DST
		,NUM_VOL_DEP
		,NUM_VOL_ARV
		,IDT_VOL
		,Ordre
	 FROM 
		(
		-- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1'
		Select 
			NUM_RTE
			--indicateur de changement de station pour chaque route
			,case 
				when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), 0) <> LCT_SCA  then  1
				else null
				end  as NUM_STA
			,COD_EVT
			,IDT_CNT_RCU
			,DAT_EVT_UTC
			,DAT_LOC_EVT
			,DAT_DEP
			,LCT_SCA
			,ESC_ORI
			,ESC_DST
			,NOM_PRN_ORI
			,NOM_PRN_DST
			,NUM_VOL_DEP
			,NUM_VOL_ARV
			,IDT_VOL
			,Ordre
		FROM 
			(
			--ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent
				Select 
				case when NUM_RTE is null then 
											-- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur
											Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null
											then 1
											Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING)
											end
										else NUM_RTE end as NUM_RTE
				,COD_EVT
				,IDT_CNT_RCU
				,DAT_EVT_UTC
				,DAT_LOC_EVT
				,DAT_DEP
				,LCT_SCA
				,ESC_ORI
				,ESC_DST
				,NOM_PRN_ORI
				,NOM_PRN_DST
				,NUM_VOL_DEP
				,NUM_VOL_ARV
				,IDT_VOL
				,Ordre
			FROM 
				(
				--ETAPE TMP3 : On affecte un numéro croissant à chaque début de route
				SELECT distinct
					 --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route
					case 
					when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
					else RTE 
					end as NUM_RTE			   
					,TMP2.COD_EVT
					,TMP2.IDT_CNT_RCU
					,TMP2.DAT_EVT_UTC
					,TMP2.DAT_LOC_EVT
					,TMP2.DAT_DEP
					,TMP2.LCT_SCA
					,TMP2.ESC_ORI
					,TMP2.ESC_DST
					,TMP2.NOM_PRN_ORI
					,TMP2.NOM_PRN_DST
					,TMP2.NUM_VOL_DEP
					,TMP2.NUM_VOL_ARV
					,TMP2.IDT_VOL
					,Ordre
				FROM
				   ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' 
					SELECT
						--On affecte un indicateur  pour chaque début de route du Receptacle
						--s il s agit d un premier evènement (sauf iSC) sans début identifié
						case 
						when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU 
												order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  ) = 1 
								AND COD_EVT <>'iSC' 
								AND COD_EVT <>'6'
						then 1
						--s il s agit bien d un début identifié
						when COD_EVT in ('74','43') 
						then 1
						-- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même
						when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), '#') in ('21', '42')  
						then 1
						else null end as RTE
					   ,TMP1.*
					   FROM
 
 
						   (
							-- ETAPE TMP1: On reccupère les information de base
							SELECT 
								'RECEPTACLE' as LVL
								,COD_EVT
								,IDT_CNT_RCU
								--,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC
								,DAT_EVT_UTC
								,DAT_LOC_EVT
								,DAT_DEP
								,LCT_SCA
								,ESC_ORI
								,ESC_DST
								,NOM_PRN_ORI
								,NOM_PRN_DST
								,NUM_VOL_DEP
								,NUM_VOL_ARV
								,IDT_VOL
								,COALESCE(C.Ordre, EVT2.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT2
							left outer join 
							(
								select '74' as Code_EVT, '1000' as Ordre
								union
								select '43' as Code_EVT, '1000' as Ordre
								union
								select '21' as Code_EVT, '0' as Ordre
								union 
								select '42' as Code_EVT, '0' as Ordre
							) C on
							EVT2.COD_EVT=C.COD_EVT
							--Receptacle level
							WHERE IDT_TYP = 'R'
 
							UNION 
 
							SELECT 
								 'CONTAINER' as LVL
								 ,EVT.COD_EVT
								,CNT.IDT_CNT_RCU
								--DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD)
								--,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC
								--,Las plus grande des deux dates :(CNT.DAT_DEB_VLD  , EVT.DAT_EVT_UTC     ) as DAT_EVT_UTC
								,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC
								,EVT.DAT_LOC_EVT
								,EVT.DAT_DEP
								,EVT.LCT_SCA
								,EVT.ESC_ORI
								,EVT.ESC_DST
								,EVT.NOM_PRN_ORI
								,EVT.NOM_PRN_DST
								,EVT.NUM_VOL_DEP
								,EVT.NUM_VOL_ARV
								,EVT.IDT_VOL
								,COALESCE(C.Ordre, EVT.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT
 
							INNER JOIN DWHFRT.STAMP_CNT_RCU CNT
									ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT
							left outer join 
							(
								select '74' as Code_EVT, '1000' as Ordre
								union
								select '43' as Code_EVT, '1000' as Ordre
								union
								select '21' as Code_EVT, '0' as Ordre
								union 
								select '42' as Code_EVT, '0' as Ordre
							) C on
							EVT.COD_EVT=C.COD_EVT
							--Container level
							WHERE EVT.IDT_TYP = 'C'
							  AND CNT.IDT_TYP = 'R'		
							) as TMP1
							--where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054'
					) as TMP2
				) as TMP3
 
			) as TMP4
		) as TMP5
	) as TMP6
 --order by 4,5
;

Pour Waldar merci pour le lien sur les fonctions de fenêtrage, j'apprends plein de chose.

PS : Je ne suis pas du tout sure que ça améliorera les perf!
Cordialement
Soazig

02/05/2018, 20h07
lotofski

1 pièce(s) jointe(s)

Bonsoir , Merci soazig vu que je peux travailler de chez moi ojr , j'ai essayé le dernier script , et j'ai le message suivant Pièce jointe 375583
02/05/2018, 21h00
lotofski

2 pièce(s) jointe(s)

Et voici le résultat attendu Pièce jointe 375599 Pièce jointe 375603

Bonjour tout le monde , voici la rps de mon sup ;

Le but est de passer de requêtes imbriquées de ce type:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 insert into TABLE_FINALE select .. from (select .. from ... (select from ... from TABLE_ORIGINE) )
A plusieurs requêtes séparées les unes après les autres:
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_1 select ... from TABLE_ORIGINE;
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_2 select ... from TABLE_INTERMEDIAIRE_1;
Code:

1 2 3 4 5 insert into TABLE_INTERMEDIAIRE_3 select ... from TABLE_INTERMEDIAIRE_2;
...
Code:

1 2 3 4 5 insert into TABLE_FINALE select ... from DERNIERE_TABLE_INTERMEDIAIRE;
puis vider les TABLE_INTERMEDIAIRE.

C’est tout.

Rien à modifier dans les conditions des requêtes.

Et à la fin il faut comparer pour vérifier que les résultats obtenus par ce script sont strictement identiques aux résultats obtenus avec le script d’origine.

03/05/2018, 11h20
al1_24

Une fois que tu auras écrit tes requêtes, si tu rencontres des problèmes de syntaxe ou d'exécution, tu pourras revenir ici pour exposer ce qui ne va pas...
03/05/2018, 12h08
SQLpro

Citation:

Envoyé par soazig

Rebonjour,
N'y-a-t-il que moi que ça choque l'utilisation de fonction de fenêtrage Min, max et count avec un order by dans le over?
Pour un ROW_NUMBER() je le conçois mais pour les min, max et count je ne vois pas à quoi il peut servir.
Par ailleurs, j'avoue mon ignorance quand aux indications

Code:

rows between 1 preceding and 1 preceding

A+
Soazig

Cela permet par exemple de faire des SUM cumulées.
Par exemple avoir le CA de la boite au jour le jour.
Ainsi le 1er janvier le CA sera les ventes du 1
Le 2 janvier, ce sera le cumul des 1 et 2 janvier et ainsi de suite...

Avec rows between ... preceding. vous pouvez, par exemple, faire une fenêtre glissante Exemple : CA "glissant" sur 7 jours (une semaine.

Lisez l'article que j'ai écrit à ce sujet :
https://sqlpro.developpez.com/articl...clause-window/
Cela vous ouvrira de nouveaux horizons

A +