passage d'une colonne dans une sous requete imbriquée

**bandoine** · 17/09/2009, 11h12

Bonjour a tous,
Voici mon premier problème du jour : réaliser un count(*) sur une requête comprenant un group by

Code sql :

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

select count(*) from masupd_attr where id_op = 150 group by dn

me renvoyai 5 ligne avec le nombre de dn correspondant alors que je souhaitai justement récupérer le nombre de lignes renvoyer (5)

j'ai donc écrit ceci

Code sql :

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

select count(*) from (select dn from masupd_attr where id_op = 150 group by dn);

qui fonctionne parfaitement bien.

Le problème est que cette requête est destinée a être une sous requête d'une requête plus grosse et que le le id_op (150) doit être en réalité une colonne dans la requête principale (je crains de mal m'exprimer il y a surement un nom pour cela mais je ne le connais pas) voici l'exemple.

Code sql :

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

1
2
3
4
5
6
7
SELECT op.id,op.user_dn,op.date_op,wmsys.wm_concat(distinct attr.attr) as attr, 
(select count (*) from (select dn from masupd_attr where id_op = op.id group by dn)) as nb_user
FROM masupd_op op INNER JOIN masupd_attr attr ON op.id = attr.id_op
WHERE id IN (SELECT id_op FROM masupd_attr WHERE attr = 'c' OR attr = 'telephonenumber' GROUP BY id_op)
HAVING op.user_DN LIKE '%ocs%'
GROUP BY op.id,op.user_dn,op.date_op
ORDER BY op.id;

lorsque j'utilise une seul sous requête (premier cas qui malheureusement ne fonctionnai pas) j'ai le droit d'utiliser op.id, mais lorsqu'il y a 2 sous requête la "variable" n'est plus disponible.
Voyez vous une solution ? je m'y prend peut être très mal et la solution est peut être évidente mais je ne trouve pas rien y fais.

**Waldar** · 17/09/2009, 11h50

Je reformate votre requête :

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
SELECT
    op.id,
    op.user_dn,
    op.date_op,
    wmsys.wm_concat(distinct attr.attr) as attr, 
    (select count (*) from (select dn from masupd_attr where id_op = op.id group by dn)) as nb_user
FROM
    masupd_op op
    INNER JOIN masupd_attr attr
      ON op.id = attr.id_op
WHERE
    op.id IN (SELECT id_op FROM masupd_attr WHERE attr = 'c' OR attr = 'telephonenumber' GROUP BY id_op)
HAVING
    op.user_DN LIKE '%ocs%'
GROUP BY
    op.id,
    op.user_dn,
    op.date_op
ORDER BY
    op.id asc;

Un having avant un group by ?
Pourquoi faire un IN sur une table que vous avez déjà dans vos jointures ?

Je vous invite à réécrire votre requête de la façon suivante :

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
SELECT
    op.id,
    op.user_dn,
    op.date_op,
    wmsys.wm_concat(distinct attr.attr) as attr, 
    att2.nb_dn as nb_user
FROM
    masupd_op op
    INNER JOIN masupd_attr attr
      ON attr.id_op = op.id
    INNER JOIN (select id_op, count(distinct dn) nb_dn from masupd_attr group by id_op) att2
      ON att2.id_op = op.id
WHERE
    attr.attr IN ('c', 'telephonenumber')
AND op.user_DN LIKE '%ocs%'
GROUP BY
    op.id,
    op.user_dn,
    op.date_op,
    att2.nb_dn
ORDER BY
    op.id asc;

**mnitu** · 17/09/2009, 11h59

Code :

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

1
2
3
 
Select col1, col, (Select … from tab2)
From tab1

remplace une jointure externe entre Tab1, et Tab2. Je pense donc qu’utiliser la jointure externe et un peu des analytiques devraient te permettre de ré écrire la requête.
En 10g une autre piste pourrait employer les collections et leur cardinalités.

**bandoine** · 17/09/2009, 12h02

Dsl pour le Having, je ne sais pas comment il s'est retrouver la dans mon post, il est a la bonne place dans mon code pourtant.

Merci beaucoup, cela marche parfaitement bien, bien plus élégant et j'imagine bien plus performant.

J'ai encore beaucoup a apprendre en SQL mais je trouve cela particulièrement intéressant.

**bandoine** · 23/09/2009, 14h53

J'ai un nouveau problème, complètement différent cette fois ci, mais je cherche juste a comprendre.

je post un script (généré par toad) car il comporte peut être des donnée intéressante pour la suite.

Code sql :

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
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
 
ALTER TABLE MGR_MASUPD.MASUPD_ATTR
 DROP PRIMARY KEY CASCADE;
 
DROP TABLE MGR_MASUPD.MASUPD_ATTR CASCADE CONSTRAINTS;
 
CREATE TABLE MGR_MASUPD.MASUPD_ATTR
(
  ID       INTEGER,
  ID_OP    INTEGER                              NOT NULL,
  DN       VARCHAR2(255 BYTE)                   NOT NULL,
  ATTR     VARCHAR2(255 BYTE)                   NOT NULL,
  OLD_VAL  VARCHAR2(255 BYTE),
  NEW_VAL  VARCHAR2(255 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
 
CREATE UNIQUE INDEX MGR_MASUPD.MASUPD_ATTR_PK ON MGR_MASUPD.MASUPD_ATTR
(ID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
ALTER TABLE MGR_MASUPD.MASUPD_ATTR ADD (
  CONSTRAINT MASUPD_ATTR_PK
 PRIMARY KEY
 (ID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

voici ma requête :

Code sql :

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
 
SELECT 
  op.id as opid,
  TO_CHAR(op.date_op,'DD/MM/YYYY HH24:MI') as opdate,
  op.user_dn as opuserdn, opdisplayname.display_name asopuserdisplayname,
  wmsys.wm_concat(DISTINCT attr.attr) AS attr,
  att2.nb_dn AS nb_user
FROM 
  masupd_op op INNER JOIN masupd_attr attr 
     ON attr.id_op = op.id 
  LEFT OUTER JOIN masupd_display_name opdisplayname
     ON op.user_dn = opdisplayname.dn
  LEFT OUTER JOIN masupd_display_name displayname
     ON attr.dn = displayname.dn
  INNER JOIN (
           SELECT 
               id_op,
               count(DISTINCT dn) nb_dn 
           FROM masupd_attr
           GROUP BY id_op
  ) att2 
    ON att2.id_op = op.id
WHERE 
    ... ... ... ... ... ... 
GROUP BY 
  op.id,
  op.date_op,
  op.user_dn,
  opdisplayname.display_name,
  att2.nb_dn 
ORDER BY 
  op.date_op DESC

suivant ce que mon php génère pour la close WHERE le temps d'exécution est soit rapide (<1s) soit très lent (plus ou moins 30s), sachant que je n'ai pas encore beaucoup de données dans mes tables ( < 3000 lignes )

voici des exemples concrets :
-

Code :

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

WHERE  op.date_op < TO_DATE('22/09/2009 10:29','DD/MM/YYYY HH24:MI')

celle ci met environ 300ms a retourner le résultat
-

Code :

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

WHERE  op.date_op < TO_DATE('23/09/2009 10:29','DD/MM/YYYY HH24:MI')

celle ci met 30 secondes.

Et j'ai plusieurs cas comme celui ci, ou le temps est soit court soit long en fonction des données dans la close where.

Est ce ma requête qui peu être optimisée, faut il que je regarde du coté des indexes (je n'ai pas encore bien compris comment cela fonctionne et je ne sais pas si cela peu/doit s'appliquer dans mon cas), est ce la structure des mes tables qui pose problème, ou est ce tout simplement normal ?

Si qqn a une piste je serai tres heureux car après avoir déjà pas mal chercher je ne trouve pas vraiment de solution pour stabiliser la requête.

**bandoine** · 23/09/2009, 15h34

Je suis entrain de lire ce document Initiation à l'optimisation de requêtes SQL sous ORACLE mais cela n'est pas évident pour en saisir tout les concepts en si peu de temps.

Je découvre le "Quest SQL Optimizer for Oracle" de Toad (ou inclue dedans en tout cas).

il me propose ce hint :

Code :

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

/*+ NO_USE_NL(ATT2,DISPLAYNAME) */

qui fais passer le temps d'exécution de 28 à 27 secondes ... ... ...

ou encore celui ci :

Code :

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

/*+ ORDERED */

qui fais passer le temps d'exécution de 28 à 32 secondes

... ... ...

j'ai l'impression encore une fois que ce n'est pas de ce coté qu'il faut chercher,... mais alors vers quel solution ce tourner, écrire un autre algorithme a base de plusieurs requêtes plus rapides coté php ?

**nuke_y** · 24/09/2009, 13h26

Tu pourrais donner le plan d'exécution des requêtes ?

Autre chose, les statistiques sont-elles à jour sur tes tables ? Le coup de la performance qui varie selon la date rentrée me fait penser à ça.

**Waldar** · 24/09/2009, 13h28

Il faut regarder l'explain plan avec le filtre au 22 septembre et comparer avec celui qui filtre au 23 septembre.

A priori, vous pourrez résoudre ce problème avec du binding, par exemple :

Code :

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

WHERE op.date_op < TO_DATE(:v ,'DD/MM/YYYY HH24:MI')

Oracle ne verra toujours que la même requête et utilisera toujours le même plan d'exécution. Evidement, il faut que celui-ci soit performant !

**mnitu** · 24/09/2009, 13h48

Envoyé par Waldar

...
A priori, vous pourrez résoudre ce problème avec du binding, par exemple :

...

Désolé, mais je ne pense pas que c’est une solution. Si vraiment tout est en ordre (méthode de mesure, statistiques, etc. ) je pense que ce type de problème se résolu très probablement par l’utilisation des histogrammes.

**nuke_y** · 24/09/2009, 14h20

En fait je pense qu'il y a déjà des histogrammes sur cette table, mais pas à jour.

**bandoine** · 28/09/2009, 10h15

effectivement l'éxecution plan il a pas l'air top :s

Et pour les stats j'ai refais un test ce matin apparemment elle sont a jour mais le problème persiste.

Code sql :

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
 
SELECT STATEMENT  ALL_ROWS 
   Cost: 60*918  Bytes: 8*373*682  Cardinality: 42*506  								
12 SORT GROUP BY  
   Cost: 60*918  Bytes: 8*373*682  Cardinality: 42*506  							
11 VIEW SYS. 
   Cost: 50*175 Bytes: 120*744*846Cardinality: 612*918  						
10 SORT GROUP BY 
   Cost: 50*175 Bytes: 232*908*840  Cardinality: 612*918  					
9 HASH JOIN  "ID_OP"="OP"."ID"
   Cost: 76  Bytes: 232*908*840  Cardinality: 612*918  				
1 TABLE ACCESS FULL MGR_MASUPD.MASUPD_ATTR 
   Cost: 19  Bytes: 356*070  Cardinality: 4*565  			
8 HASH JOIN RIGHT OUTER  "ATTR"."DN"="DISPLAYNAME"."DN"(+)
   Cost: 30  Bytes: 1*378*630  Cardinality: 4*565  			
2 INDEX FAST FULL SCAN MGR_MASUPD.MASUPD_DISPLAY_NAME_PK 
   Cost: 3  Bytes: 17*556  Cardinality: 231 
7 HASH JOIN  "ATTR"."ID_OP"="OP"."ID"
   Cost: 26  Bytes: 1*031*690  Cardinality: 4*565  		
5 HASH JOIN OUTER  "OP"."USER_DN"="OPDISPLAYNAME"."DN"(+)
   Cost: 7  Bytes: 4*318  Cardinality: 34  	
3 TABLE ACCESS FULL MGR_MASUPD.MASUPD_OP "OP"."DATE_OP">TIMESTAMP' 2009-09-25 00:00:00'
   Cost: 3  Bytes: 1*734  Cardinality: 34  
4 TABLE ACCESS FULL MGR_MASUPD.MASUPD_DISPLAY_NAME 
   Cost: 3  Bytes: 17*556  Cardinality: 231  
6 TABLE ACCESS FULL MGR_MASUPD.MASUPD_ATTR 
   Cost: 19  Bytes: 451*935  Cardinality: 4*565

Je sais pas pourquoi, c'est le premier plan que je lis, mais il a pas l'air top ce plan non ?

Code sql :

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
 
SELECT STATEMENT  ALL_ROWSCost: 
   354  Bytes: 1*387*760  Cardinality: 4*565  								
12 SORT GROUP BY  
   Cost: 354  Bytes: 1*387*760  Cardinality: 4*565  							
11 HASH JOIN RIGHT OUTER  "ATTR"."DN"="DISPLAYNAME"."DN"(+)
   Cost: 51  Bytes: 1*387*760  Cardinality: 4*565  						
1 INDEX FAST FULL SCAN MGR_MASUPD.MASUPD_DISPLAY_NAME_PK
   Cost: 3  Bytes: 17*094  Cardinality: 231  					
10 HASH JOIN  "ATTR"."ID_OP"="OP"."ID"
   Cost: 47  Bytes: 1*049*950  Cardinality: 4*565  					
8 HASH JOIN OUTER  "OP"."USER_DN"="OPDISPLAYNAME"."DN"(+)
   Cost: 28  Bytes: 4*862  Cardinality: 34  				
6 HASH JOIN  "ATT2"."ID_OP"="OP"."ID"
   Cost: 24  Bytes: 2*278  Cardinality: 34  			
2 TABLE ACCESS FULL MGR_MASUPD.MASUPD_OP 
   Cost: 3  Bytes: 1*734  Cardinality: 34  		
5 VIEW MGR_MASUPD. 
   Cost: 21  Bytes: 544  Cardinality: 34  		
4 SORT GROUP BY  
   Cost: 21  Bytes: 2*652  Cardinality: 34  	
3 TABLE ACCESS FULL MGR_MASUPD.MASUPD_ATTR 
   Cost: 19  Bytes: 356*070  Cardinality: 4*565  
7 TABLE ACCESS FULL MGR_MASUPD.MASUPD_DISPLAY_NAME 
   Cost: 3  Bytes: 17*556  Cardinality: 231  			
9 TABLE ACCESS FULL MGR_MASUPD.MASUPD_ATTR 
   Cost: 19  Bytes: 397*155  Cardinality: 4*565

Celui ci c'est le plan lorsqu'on supprime la clause where pour récupérer toute les lignes et qui s'exécute rapidement.

**bandoine** · 28/09/2009, 11h45

Voila quelques infos supplémentaire qui vous donneront peut être une meilleure idée du problème.

Code sql :

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

 WHERE LOWER(displayname.display_name) LIKE '%fox%'

Plan Cost : 3777
Execution time : 20ms

Code sql :

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

 WHERE op.date_op < TO_DATE('26/09/2009', 'DD/MM/YYYY')

Plan Cost : 60918
Execution time : 38.820s

Code sql :

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

 WHERE attr.attr IN ('c')

Plan Cost : 6695
Execution time : 4.190s

Code sql :

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

 WHERE LOWER(displayname.display_name) LIKE '%fox%' AND attr.attr IN ('c')

Plan Cost : 450
Execution time : 0ms

Code sql :

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

 sans clause where

Plan Cost : 354
Execution time : 80ms

**bandoine** · 01/10/2009, 15h30

Après avoir trouver un walkaround plus que dégueu mais qui fonctionnais très bien

Code :

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

 WHERE 1=1 OR ... ... ... ...

C'est apparemment enfait l'optimiseur qui était paumé avec l'écriture avec la nouvelle norme ANSI j'ai donc réécrit ma requête avec l'autre norme et tout est rentrer dans l'ordre.

Code sql :

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
SELECT 
  op.id AS opid,
  TO_CHAR(op.date_op,'DD/MM/YYYY HH24:MI') AS opdate,
  op.user_dn AS opuserdn,
  opdisplayname.display_name as opuserdisplayname,
  wmsys.wm_concat(DISTINCT attr.attr) AS attr,
  att2.nb_dn AS nb_user
FROM 
  masupd_op op,
  masupd_attr attr,
  masupd_display_name displayname,
  masupd_display_name opdisplayname,
  (SELECT 
     id_op,
     count(DISTINCT dn) nb_dn 
   FROM masupd_attr
   GROUP BY id_op
  ) att2
WHERE 
    attr.id_op = op.id 
    AND op.user_dn(+) = opdisplayname.dn 
    AND attr.dn(+) = displayname.dn 
    AND att2.id_op = op.id
    AND op.date_op < TO_DATE('26/09/2009', 'DD/MM/YYYY')
GROUP BY 
  op.id,
  op.date_op,
  op.user_dn,
  opdisplayname.display_name,
  att2.nb_dn 
ORDER BY 
  op.date_op DESC

Merci a tout ceux qui ont essayer de m'aider.

passage d'une colonne dans une sous requete imbriquée

SQL Oracle

Discussions similaires

Partager

Partager