Discussion :

[Keuf95]

Oracle 8i
La table Histactu comporte environ 9 millions d'enregistrements
Un exemple des données contenues dans la table histactu.
CODAGT MOISPAIE CODRUB MONTANT CODETAT
1 012005 10 1000 A
1 012005 10 1050 N
2 012005 10 2000 A
3 012005 10 1500 A
3 012005 10 1560 N
Le but de la vue est de récupérer pour chaque enregistrement la ligne ou le CODETAT = N lorsque celui-ci existe et s'il n'existe pas prendre la ligne avec le CODETAT = A.
D'après mon exemple le résultat attendu est le suivant :
CODAGT MOISPAIE CODRUB MONTANT CODETAT
1 012005 10 1050 N
2 012005 10 2000 A
3 012005 10 1560 N

J'utilise le code ci-dessous pour crééer une vue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Create view view_histactu as
select *
 from
  histactu hist1
where
  hist1.ANMOIS > 200500 and
  codetat in (select max(hist2.codetat)
    from 
      histactu hist2
    where
      hist2.ANMOIS > 200500 and
      hist2.U##CODCOL = hist1.U##CODCOL and
      hist2.DATMAJ = hist1.DATMAJ and
      hist2.U##HHMMSS = hist1.U##HHMMSS and
      hist2.DTEDEB = hist1.DTEDEB and
      hist2.DTEFIN  = hist1.DTEFIN
    group by
      hist2.U##CODCOL, hist2.CODAGT, hist2.ANMOIS, 
      hist2.CODRUB,hisst2.DATMAJ, hist2.U##HHMMSS, hist2.DTEDEB, 
      hist2.DTEFIN)

Lorsque je lance ensuite la requête suivante les temps de réponse sont très longs (environ 25mn)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
select 
  count(*)
from 
  VIEW_HISTACTU
where 
  codagt = 1

Peut on optimiser la requête Sql qui est utilisée dans la création de la vue.
Merci pour vos réponses.

[SQLpro]

1) quel est le discrimant des lignes ? CODAGT + MOISPAIE + CODRUB ???
2) d'ou viennent : U##CODCOL, U##HHMMSS, ANMOIS, DATEMAJ, DTEDEB , DTEFIN ???

Sans ces données veut requête ne veut rien dire.

SVP respectez ces règles : http://www.developpez.net/forums/viewtopic.php?t=32668

A +

[Keuf95]

Dans mon exemple je n'ai pas cité l'ensemble des champs de la table histactu, les voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 Nom                  Type
 ------------------ ----------------------------
 U##CODCOL   VARCHAR2(5)
 CODCOL          VARCHAR2(5)
 CODAGT          NUMBER
 U##CODORG   VARCHAR2(3)
 CODORG          VARCHAR2(3)
 CODRUB           NOT NULL NUMBER
 DTEDEB            DATE
 DTEFIN             DATE
 MONTANT          NUMBER
 TAUX                NUMBER
 BASE                NUMBER
 COEFF               NUMBER
 FLGORI              UMBER
 DTEREP             DATE
 MOISPAIE          NUMBER
 FLGCAL              NUMBER
 FLGVAL              NUMBER
 U##CODTRN      VARCHAR2(1)
 CODTRN             VARCHAR2(1)
 U##CODETAT     NOT NULL VARCHAR2(3)
 CODETAT            NOT NULL VARCHAR2(3)
 DATMAJ               DATE
 U##HHMMSS        VARCHAR2(30)
 HHMMSS               VARCHAR2(30)
 ANMOIS                NUMBER

La clé est constituée de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
U##CODCOL 
DATMAJ    
U##HHMMSS
DTEDEB 
DTEFIN

Merci pour vos réponses

[Médiat]

Je ne suis pas certain d'avoir tout compris, mais essaye :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT * 
FROM   histactu hist1 
WHERE  hist1.ANMOIS > 200500 
  AND  codetat = (SELECT MAX(hist2.codetat) 
                   FROM   histactu hist2 
                   WHERE  hist2.ANMOIS    = hist1.ANMOIS 
                     AND  hist2.U##CODCOL = hist1.U##CODCOL 
                     AND  hist2.DATMAJ    = hist1.DATMAJ 
                     AND  hist2.U##HHMMSS = hist1.U##HHMMSS 
                     AND  hist2.DTEDEB    = hist1.DTEDEB 
                     AND  hist2.DTEFIN    = hist1.DTEFIN )

[SQLpro]

Commencez par réformer votre clef. Elle comporte au max 41 octets => 10 mot du processeur. De plus elle est composite (plusieurs colonnes) et multitypé (char, date...) !!! De plus les colonnes la composant sont dispersées au lieu d'être regroupées de manière contigues. Pour couronner le tout vous utilisez systématiquement du varchar pour toutes vos colonnes, y compris celles situées entre les colonnes composant la clef... Cela induit des effets de lecture en "zigzag" si des mise à jour sont faites dans la table.

Le tout impose vraisemblablement une vingtaine de passes dans le processeur pour chaque clef scrutée !

Si votre clef était une unique colonne de type entier, vous gagneriez déjà énormément...

A lire sur l'optimisation : http://sqlpro.developpez.com/cours/optimiser/

A +

[Keuf95]

Je ne suis pas certain d'avoir tout compris, mais essaye :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT * 
FROM   histactu hist1 
WHERE  hist1.ANMOIS > 200500 
  AND  codetat = (SELECT MAX(hist2.codetat) 
                   FROM   histactu hist2 
                   WHERE  hist2.ANMOIS    = hist1.ANMOIS 
                     AND  hist2.U##CODCOL = hist1.U##CODCOL 
                     AND  hist2.DATMAJ    = hist1.DATMAJ 
                     AND  hist2.U##HHMMSS = hist1.U##HHMMSS 
                     AND  hist2.DTEDEB    = hist1.DTEDEB 
                     AND  hist2.DTEFIN    = hist1.DTEFIN )

Cette proposition ne renvoie que les enregistrements suivants voir exemple au début de mon post :
1 012005 10 1050 N
3 012005 10 1560 N
Il manque l'enregistrement suivant :
2 012005 10 2000 A

Merci pour ta réponse.

[Keuf95]

Commencez par réformer votre clef. Elle comporte au max 41 octets => 10 mot du processeur. De plus elle est composite (plusieurs colonnes) et multitypé (char, date...) !!! De plus les colonnes la composant sont dispersées au lieu d'être regroupées de manière contigues. Pour couronner le tout vous utilisez systématiquement du varchar pour toutes vos colonnes, y compris celles situées entre les colonnes composant la clef... Cela induit des effets de lecture en "zigzag" si des mise à jour sont faites dans la table.

Le tout impose vraisemblablement une vingtaine de passes dans le processeur pour chaque clef scrutée !

Si votre clef était une unique colonne de type entier, vous gagneriez déjà énormément...

A lire sur l'optimisation : http://sqlpro.developpez.com/cours/optimiser/

A +

Je suis tout à fait d'accord sur le manque évident de performances que représente une table avec cette structure. Malheureusement je ne suis pas à l'origine de la conception et n'ai pas les moyens d'intervenir dessus.
J'ai un peu progressé avec le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT
HA.CODCOL,                 
HA.CODAGT,                
HA.CODORG,               
HA.CODRUB,              
HA.DTEDEB,             
HA.DTEFIN,            
decode(upper(HN.CODETAT),'N',HN.MONTANT,HA.MONTANT) montant,
decode(upper(HN.CODETAT),'N',HN.TAUX   ,HA.TAUX )   taux,
decode(upper(HN.CODETAT),'N',HN.BASE   ,HA.BASE )   base,
HA.COEFF  ,        
HA.FLGORI ,       
HA.DTEREP ,      
HA.MOISPAIE,     
HA.FLGCAL  ,    
HA.FLGVAL  ,   
HA.CODTRN  ,  
decode(upper(HN.CODETAT),'N',HN.CODETAT,HA.CODETAT) codetat , 
HA.DATMAJ  ,
HA.HHMMSS  ,
HA.ANMOIS
FROM
  pers.HistActu HA,
  pers.HistActu HN
WHERE HN.CODAGT (+) = HA.CODAGT
    and HN.U##CODCOL (+) = HA.U##CODCOL
    and HN.DATMAJ (+) = HA.DATMAJ
    and HN.U##HHMMSS (+) = HA.U##HHMMSS
    and HN.CODRUB (+) = HA.CODRUB
    and HN.anmois (+) = HA.anmois
    and HN.FLGVAL (+) = HA.FLGVAL
    and ( upper(HA.codetat) = 'A')
AND ( nvl(HN.FLGVAL,1) = 1 )
and ( HN.codetat (+) <> 'a' AND HN.codetat (+) <> 'A')

Mais cette solution bien que plus performante ne renvoie pas tous les enregistrements voulus :
La requête retourne :
Les enregistrements avec codetat = "A" si il n'existe pas de codetat = "N"
Les enregistrements avec codetat = "N" si il existe un codetat = "A"

Il manque :
Les enregistrements avec codetat = "N" si il n'existe pas de codetat = "A"
Merci pour vos réponses.

[Médiat]

Comme je ne maîtrise pas toutes tes données, j'ai recopié ta liste, mais si je juge par tes exemples :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * 
FROM   histactu hist1 
WHERE  hist1.ANMOIS > 200500 
  AND  codetat = (SELECT MAX(hist2.codetat) 
                   FROM   histactu hist2 
                   WHERE  hist2.ANMOIS    = hist1.ANMOIS 
                     AND  Les liens nécessaires entre hist1 et hist2, c'est à dire tout ce qu'il y a de commun entre la ligne avec A et la ligne avec B (il n'y a sans doute ni la date ni l'heure, mais je ne peux pas savoir...)

[Keuf95]

Comme je ne maîtrise pas toutes tes données, j'ai recopié ta liste, mais si je juge par tes exemples :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * 
FROM   histactu hist1 
WHERE  hist1.ANMOIS > 200500 
  AND  codetat = (SELECT MAX(hist2.codetat) 
                   FROM   histactu hist2 
                   WHERE  hist2.ANMOIS    = hist1.ANMOIS 
                     AND  Les liens nécessaires entre hist1 et hist2, c'est à dire tout ce qu'il y a de commun entre la ligne avec A et la ligne avec B (il n'y a sans doute ni la date ni l'heure, mais je ne peux pas savoir...)

Je pense que le problème n'est pas dans les liens, c'est simplement qu'il n'existe pas de "group by" dans la sous requête, à partir de ce moment le MAX(CODETAT) est égal à "N" systématiquement mais sinon effectivement la clause "=" est plus performante que mon "in" mais cela reste malgré tout trop long.
Merci pour ta réponse.

[Médiat]

Je pense que le problème n'est pas dans les liens, c'est simplement qu'il n'existe pas de "group by" dans la sous requête, à partir de ce moment le MAX(CODETAT) est égal à "N" systématiquement

Pas besoin de GROUP BY, le WHERE doit faire tout le boulot, si je m'en tiens à ton exemple du début, je mettrais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
WHERE hist2.MOISPAIE    = hist1.MOISPAIE
  AND hist2.CODRUB    = hist1.CODRUB

[Keuf95]

Merci, Médiat c'est tout de même plus rapide que ma requête de départ et ça marche . De toutes façons je ne pense pas que je puisse obtenir beaucoup mieux vu la structure de la table.
Pour te donner plus de détails je me connecte ensuite sur cette vue Oracle à l'aide d'ACCESS en ODBC et les temps de réponse étaient tellement long que cela générait une erreur au niveau de la connexion ODBC, maintenant ça fonctionne.
A+