choix entre des requetes equivalentes

**mnr_nfo** · 19/02/2008, 09h52

Bonjour tlm,
en fait, il s'agit de choisir la requete la plus optimisé parmi les suivantes (vu que j'ai des problemes avec le EXPLAIN PLAN

)
la premiere
SELECT
SUM(CASE status WHEN 0 THEN 1 ELSE 0 END) AS NotCompleted,
SUM(CASE status WHEN 1 THEN 1 ELSE 0 END) AS Completed
FROM ***
WHERE ***

la deuxieme

SELECT
COUNT(CASE WHEN status= 0 THEN 1 END) AS Notcompleted,
COUNT(CASE WHEN status = 1 THEN 1 END) AS Completed
FROM ***
WHERE ***

autre chose, est ce que les appelles des fonction SUM et COUNT dans un meme SELECT engendre un seul parcours de la table ou bien autant de fois que l'appel à ces fonction ?
si vous avez d'autres propositions n'hesitez surtttttttou pas

Merci d'avance pour votre aide

**McM** · 19/02/2008, 09h55

Niveau perfs ce sera pareil.

Les fonctions d'aggrégation sont faites pour.
Donc 1 seul parcours de la table.
Le tri se fait en mémoire.

**mnr_nfo** · 19/02/2008, 10h11

existe -il une solution avec un seul COUNT ou un seul SUM par requete ? (tout en ayant le meme resultat bien sur )

**McM** · 19/02/2008, 10h35

Et comment tu fais pour avoir 2 colonnes ?

Pourquoi chercher à n'avoir qu'un sum par requete ?

**mnr_nfo** · 19/02/2008, 10h40

c'est justement la contrainte que j'ai,parceque la requête sera faite sur une table contenant qlq millions de ligne donc ça doit être optimisé "as possible"

**McM** · 19/02/2008, 11h34

C'est optimisé.

**Tracnac** · 19/02/2008, 12h16

Hello,

Combien d'état peut avoir la colonne status ? et est elle "not null" ?

Cordialement.

**mnr_nfo** · 19/02/2008, 12h24

en effet, status est une colonne not null et peut avoir une dizaine d'états(c'est parametrable)

**McM** · 19/02/2008, 17h00

Le mieux est d'utiliser DECODE plutôt que CASE pour les cas simples

Code :

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

1
2
3
SELECT
SUM(DECODE(status,0,1,0)) AS NotCompleted,
SUM(DECODE(status,1,1,0)) AS Completed

**Michel SALAIS** · 20/02/2008, 06h19

Envoyé par mnr_nfo

en effet, status est une colonne not null et peut avoir une dizaine d'états(c'est parametrable)

Alors cet exemple peut être utile

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
drop table test;
 
purge recyclebin;
 
create table test (a number not null, b timestamp default systimestamp);
 
insert into test (a)
select round(dbms_random.value(1, 10))
from dual
connect by level <= 10000000;
 
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS
  (
      ownname => 'scott'
    , tabname => 'test'
    , estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE
    , method_opt => 'for columns a size 10'
  );
END;
/

Voici un récapitulatif de la situation de la table

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
select a, count(*) cnt
from test
group by a;
 
A   CNT                                                                                                                             
--  -------                                                                                                                              
1   555010                                                                                                                             
6   1111470                                                                                                                             
2   1109633                                                                                                                             
4   1111134                                                                                                                             
5   1112228                                                                                                                             
8   1112261                                                                                                                             
3   1111613                                                                                                                             
7   1110465                                                                                                                             
9   1109955                                                                                                                             
10  556231

les résultats de l'exécution avec set autotrace on sans index

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
36
37
38
39
40
41
42
43
SQL> select 
  2    count(case when a = 1 then 1 end) cnt_1,
  3    count(case when a = 2 then 1 end) cnt_2
  4  from test;
SQL> /
 
     CNT_1      CNT_2
---------- ----------
    555010    1109633
 
Ecoulé : 00 :00 :05.09
 
Plan exécution
----------------------------------------------------------
Plan hash value: 1950795681
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |  2340  (10)| 00:00:27 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |  1651K|  4837K|  2340  (10)| 00:00:27 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"=1 OR "A"=2)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      27701  consistent gets
      18777  physical reads
          0  redo size
        399  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Maintenant, nous créons un index sur la colonne "a"

Code :

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

create index ind_a on test (a);

Et ci-après les résultats de l'exécution avec SQL*Plus (toujours autotrace on)

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
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
SQL> select
  2    count(case when a = 1 then 1 end) cnt_1,
  3    count(case when a = 2 then 1 end) cnt_2
  4  from test;
 
     CNT_1      CNT_2
---------- ----------
    555010    1109633
 
Ecoulé : 00 :00 :02.28
 
Plan exécution
----------------------------------------------------------
Plan hash value: 2317360725
 
-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  1536   (8)| 00:00:18 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_A |  9862K|    28M|  1536   (8)| 00:00:18 |
-------------------------------------------------------------------------------
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19594  consistent gets
          0  physical reads
          0  redo size
        399  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> 
SQL> select
  2    count(case when a = 1 then 1 end) cnt_1,
  3    count(case when a = 2 then 1 end) cnt_2
  4  from test
  5  where a in (1, 2);
 
     CNT_1      CNT_2
---------- ----------
    555010    1109633
 
Ecoulé : 00 :00 :00.64
 
Plan exécution
----------------------------------------------------------
Plan hash value: 2317360725
 
-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  1615  (12)| 00:00:19 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_A |  1651K|  4837K|  1615  (12)| 00:00:19 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"=1 OR "A"=2)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3257  consistent gets
          0  physical reads
          0  redo size
        399  bytes sent via SQL*Net to client
        377  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

Nous pouvons constater que l'ajout de l'index et de la clause WHERE améliorent sensiblement les performances compte tenu de la distribution des valeurs dans la colonne "a" de cette table

**mnr_nfo** · 20/02/2008, 11h47

Merci Michel pour votre reponse, seulement dans notre cas réelle
les "count" de "a" dans la table test que vous avez utilisé ne sont pas calculé au préalable
en d'autre termes, la table test contient :
id status .....
----------
1 1
2 1
3 1
4 1
5 2
6 3
7 5
8 2
.
.
.
et donc il faut calculer l'occurence de chaque status en utilisant le moins de resources avec un temps de reponse acceptable (pour une table géante)
Merci

**Michel SALAIS** · 20/02/2008, 11h55

Envoyé par mnr_nfo

Merci Michel pour votre reponse, seulement dans notre cas réelle
les "count" de "a" dans la table test que vous avez utilisé ne sont pas calculé au préalable
en d'autre termes, la table test contient :
id status .....
----------
1 1
2 1
3 1
4 1
5 2
6 3
7 5
8 2
.
.
.
et donc il faut calculer l'occurence de chaque status en utilisant le moins de resources avec un temps de reponse acceptable (pour une table géante)
Merci

Je ne suis pas sur d'avoir bien compris parce que dans mon exemple, il n'y a rien qui est calculé au préalable!

Ma table test possède 10000000 de lignes.

**Tracnac** · 20/02/2008, 12h01

Code :

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

1
2
3
4
5
 
INSERT INTO test (a)
SELECT round(dbms_random.value(1, 10))
FROM dual
connect BY level <= 10000000;

Superbe moi qui m'embête toujours avec mes LOOP pour mes examples...

Merci Michel.

choix entre des requetes equivalentes

SQL Oracle

Discussions similaires

Partager

Partager