Discussion :

[tommey]

Salut, j'ai une requête à améliorer.

Admettons une requête du genre et que j'ai un index sur le champ status.

Code :

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

1
2
3
SELECT * FROM TABLE1 
WHERE STATUS IN (1, 2)
ORDER BY ID

Dans ce cas mon index n'est pas utilisé et le "explain plan" m'indique un full table scan.

Si je fais seulement un

Code :

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

1
2
3
SELECT * FROM TABLE1 
WHERE STATUS IN (2)
ORDER BY ID

mon index est utilisé et le résultat s'affiche beaucoup plus vite que si je n'avais pas d'index du tout sur le champ status.

Même principe avec STATUS = 1 vs STATUS = 1 OR STATUS = 2.

J'ai besoin d'avoir de quoi de performant pour répondre à une table d'un millions d'enregistrements avec un IN (1, 2, ...)


J'ai essayé de forcer l'index avec

Code :

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

1
2
select /*+ INDEX(a ix_test) */ from table1 a
where status in (1,2)...

ça change rien.

La seule chose que j'ai trouvé qui est vraiment mais alors là vraiment performant, c'est de créer ma table en faisant des partitions basées sur la colonne statut.

Si je fais ça et je fais un in (1, 2, 4) là les partitions et les index font que ça reviens vraiment vite. Par contre pour utilisé le partitionning il faut une license qui vaut assez cher...

Donc, je me demandais si quelqu'un aurait une idée pour m'aider (sans partionning).

Merci !

[FSiebert]

Bonjour,

C'est normal que IN et OR fasse la même chose.

La solution pour utiliser l'index serait de faire

Code :

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

1
2
3
4
5
6
7
SELECT * FROM TABLE1
WHERE STATUS = 1
ORDER BY ID
UNION ALL
SELECT * FROM TABLE1
WHERE STATUS = 2
ORDER BY ID

Mais c'est probablement moins performant (ou en tout cas le coût estimé est moins bon) sinon je pense que l'optimiseur aurait fait la réécriture tout seul.

Je ne suis pas sûr qu'il y ait beaucoup de solution, la requête à faire est telle qu'elle est et on ne peut parfois pas vraiment optimiser.
Quelle est la sélectivité des statuts ?

[Mohamed.Houri]

Vous feriez mieux de poster les deux plans d'exécution pour appréciation. Il suffit parfois d'un seul enregistrement de plus pour faire basculer une exécution d'un index range scan vers un full table scan

philosophy

[Rei Ichido]

Donc, je me demandais si quelqu'un aurait une idée pour m'aider (sans partionning).

Comme le dit Mohammed.Mouri, il faudrait voir les traces et les plans pour pouvoir être précis dans le diagnostic. Cependant, il est probable que les statistiques remontent à l'optimiseur que les valeurs 1 et 2 du statut occupent une très forte volumétrie dans la table. Et donc qu'il n'est pas forcément pertinent de faire :
- un scan d'index qui remonte de toute façon une très grosse partie de la table
- à partir de l'index, aller chercher les infos dans la table
- et accessoirement à la fin trier le tout.

Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).

[FSiebert]

Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).

Oups, effectivement, je n'avais pas du tout fait attention à l'ORDER BY
Bien vu, merci

[Rams7s]

Une remarque en passant : la requête avec les UNION n'est pas valable, car rien ne garantira l'ordre final (il faudrait placer le order by dans un select englobant).

C'est faux.

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
TMP@MINILHC >select * from tmp;
 
         N         N2
---------- ----------
         1          2
         1          1
         2          1
 
Elapsed: 00:00:00.23
TMP@MINILHC >select * from tmp union all select * from tmp union all select * from tmp order by 1,2 desc;
 
         N         N2
---------- ----------
         1          2
         1          2
         1          2
         1          1
         1          1
         1          1
         2          1
         2          1
         2          1
 
9 rows selected.
 
Elapsed: 00:00:00.01
TMP@MINILHC >select * from tmp union all select * from tmp union all select * from tmp order by 1 desc,2;
 
         N         N2
---------- ----------
         2          1
         2          1
         2          1
         1          1
         1          1
         1          1
         1          2
         1          2
         1          2
 
9 rows selected.

La solution pour utiliser l'index serait de faire [... UNION ALL ... ]

Et le debut est faux aussi, vu que le IN peut tres bien utiliser l'index:
(lignes 45 et 71 pour la difference)

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
 
TMP@MINILHC >create table tmp as select case mod(level,2) when 0 then 0 else level end n,lpad(level,8,'x') vc from dual connect by level<1e6;
 
Table created.
 
Elapsed: 00:00:04.12
TMP@MINILHC >create index tmp_ind on tmp(n);
 
Index created.
 
Elapsed: 00:00:04.04
TMP@MINILHC >insert into tmp select * from tmp;
999999 rows created.
 
Elapsed: 00:00:50.31
TMP@MINILHC >set autotrace traceonly
 
TMP@MINILHC >exec dbms_stats.gather_table_stats(user,'TMP')
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:05.64
TMP@MINILHC >exec dbms_stats.gather_index_stats(user,'TMP_IND')
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.71
TMP@MINILHC >select * from tmp where n in (0,13);
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report
 
1000000 rows selected.
 
SP2-0612: Error generating AUTOTRACE report
Elapsed: 00:00:03.37
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3389504856
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   976K|    11M|  1372   (3)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| TMP  |   976K|    11M|  1372   (3)| 00:00:17 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("N"=0 OR "N"=13)
 
SP2-0612: Error generating AUTOTRACE STATISTICS report
TMP@MINILHC >select * from tmp where n in (2,13);
Error ORA-942 while gathering statistics
SP2-0612: Error generating AUTOTRACE report
 
SP2-0612: Error generating AUTOTRACE report
Elapsed: 00:00:00.00
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3354077773
 
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     4 |    48 |     6   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TMP     |     4 |    48 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TMP_IND |     4 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("N"=2 OR "N"=13)
 
SP2-0612: Error generating AUTOTRACE STATISTICS report

Et dans le cas ou il s'agit d'utiliser l'index pour une seule des deux valeurs, je vois mal l'interet vu que le FTS de la premiere valeur va lire tout la table toutes manieres

[ojo77]

A priori le calcul d'un histogramme sur la colonn status devrait résoudre le problème.

La question que se pose l'otimiseur est de savoir combien de blocs de données doivent être remontés de la table pour la contrainte status=1 et pour contrainte status=2.

Vous dites avoir 1 million de lignes. Je suppose le nombre de status plus limité (mettons 10) je suppose aussi que le status est toujours renseigné et je suppose encore que vous n'avez pas d'histogramme à votre disposition et je suppose enfin que vous avez 100 lignes par bloc (donc 10 000 blocs).

L'optimiseur va alors se demander combien de lignes sont sensées être remontées pour chaque valeur. Ne s'appuyant que sur ses statistiques il va obtenir le résultat de 200 000.

Il va ensuite, s'il en a la possibilité estimer la dispersion des données dans le segment (au travers des blocs). Pour cela il va s'appuyer sur la statistique clustering factor de l'index sur la colonne s'il existe. (sinon il estime qu'il y a une distribution équilibrée des données). Plus la table est triée dans l'ordre des données de votre colonne plus l'optimiseur a de chance de prendre l'index).

Dans notre exemple, si le données sont triées dans la table comme dans l'index, il aura à lire 2000 blocs de la table et les blocs de l'index (mettons 1000) soit en tout 3000 blocs => il choisira alors l'index.

Si par contre les données sont plus uniformément réparties dans les blocs, il estimera qu'il doit lire les 10 000 blocs de la table plus éventuellement les 3 000 blocs d'index s'il doit le prendre. Il choisira donc de ne pas le prendre.

Je pense que c'est quelque chose de cet ordre qui se passe pour votre requête.

Vous dites avoir constaté que le passage par index est plus rapide. C'est donc que l'optimiseur ne dispose pas des informations suffisantes pour avoir une bonne estimation du nombre de blocs à lire pour effectuer votre requête.

Le positionnement d'un histogramme sur la colonne status améliorera son estimation du nombre de lignes ramenées et pourra éventuellement refaire basculer le plan d'exécution vers un passage par index.

J'ai fait beaucoup de suppositions dans ma réponse, mieux connaitre vos statistiques et la topologie de votre (vos) tables permettrait un meilleur diagnostique. D'où la demande de M. Houri de vos plans d'exécution.

La version d'oracle que vous utilisez pourrait aussi être utile

[tommey]

Bonjour à tous et merci pour vos réponses.

Pour commencer voici les counts de chaque status :

Code :

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

1
2
3
  SELECT status, COUNT (*)
    FROM T_CON002_INPUT_PAYLOAD
GROUP BY status

Résultat :

STATUS COUNT(*)
1 6837
2 252408
4 953
3 6313
0 698438


Ensuite si j'essaie :

Code :

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

1
2
3
select * from  T_CON002_INPUT_PAYLOAD
where status in (1)
order by id

Voici le plan d'exécution :

Plan
SELECT STATEMENT ALL_ROWSCost: 106 737 Bytes: 25 281 690 Cardinality: 192 990
3 SORT ORDER BY Cost: 106 737 Bytes: 25 281 690 Cardinality: 192 990
2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 076 Bytes: 25 281 690 Cardinality: 192 990
1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990


Maintenant si je fais :

Code :

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

1
2
3
select * from  T_CON002_INPUT_PAYLOAD
where status in (1, 2)
order by id

Voici le plan :
Plan
SELECT STATEMENT ALL_ROWSCost: 146 684 Bytes: 50 563 380 Cardinality: 385 980
2 SORT ORDER BY Cost: 146 684 Bytes: 50 563 380 Cardinality: 385 980
1 TABLE ACCESS FULL TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 135 364 Bytes: 50 563 380 Cardinality: 385 980


Vous avez raison par contre pour le union all, ça semble donner de bon résultat quand même.

Voici mon test :

Code :

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

1
2
3
4
5
6
7
8
select * from (
select * from T_CON002_INPUT_PAYLOAD A
WHERE STATUS =4
union all
select * from T_CON002_INPUT_PAYLOAD A
WHERE STATUS =1)
where create_date > '2012-08-20'
ORDER BY create_date

Et le plan :
Plan
SELECT STATEMENT ALL_ROWSCost: 328 072 Bytes: 256 676 434 Cardinality: 1 929 898
7 SORT ORDER BY Cost: 328 072 Bytes: 256 676 434 Cardinality: 1 929 898
6 VIEW CON3FEF01. Cost: 270 751 Bytes: 256 676 434 Cardinality: 1 929 898
5 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 078 Bytes: 25 281 690 Cardinality: 192 990
1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990
4 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 078 Bytes: 25 281 690 Cardinality: 192 990
3 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990


Attention, ne considérer pas trop le nombre de bytes que ma requête retourne car j'ai un champ clob qui contient un gros xml pour chaque enregistrement.

Bref, je pense que je vais essayer de me débrouiller avec le union all, et je vous reviens si ça ne fonctionne pas...

Merci à tous

[ojo77]

Ok,

On constate donc bien que vous n'avez pas d'histogramme: il considère une cardinalité égale au nombre de lignes (moins les nulls) total de la base divisé par le nombre de valeurs distinctes, en l'occurrence 192 990 .

Si l'approximation est bonne pour status=2 elle ne l'est pas pour les autres valeurs => positionnez un histogramme ça peut fonctionner. Attention cependant, il y a 1 quart des valeurs qui valent 2, il reste quand même de fortes chances que le choix de l'optimiseur s'oriente vers un FTS.

Questions

Le paramètre "_no_or_expansion" est-il positionné à true ?
Vos statistiques systèmes sont-elles calculées ?
Quelle est la valeur du paramètre optimizer_index_cost_adj ?
Quelle est la valeur du paramètre dbfile_multibloc_read_count ?

Quel plan est pris par

Code :

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

1
2
3
SELECT * FROM  T_CON002_INPUT_PAYLOAD
WHERE STATUS IN (2)
ORDER BY id

?

[Mohamed.Houri]

Quelle version d'Oracle?

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
 
STATUS COUNT(*)
1 6837
2 252408
4 953
3 6313
0 698438
 
SELECT * FROM  T_CON002_INPUT_PAYLOAD
WHERE STATUS IN (1)
ORDER BY id Voici le plan d'exécution :
 
Plan
SELECT STATEMENT ALL_ROWSCost: 106 737 Bytes: 25 281 690 Cardinality: 192 990 
3 SORT ORDER BY Cost: 106 737 Bytes: 25 281 690 Cardinality: 192 990 
2 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 101 076 Bytes: 25 281 690 Cardinality: 192 990 
1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 362 Cardinality: 192 990

(a) Le nombre d'enregistrement total semble donc être = 964949
(b) Comme il existe 5 valeurs distinctes de la colonne status sa selectivité est égale à 1/5
(c) La cardinality du premier select est donc card = (a) * (b) = 964949/5 = 192990

Code :

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

1
2
3
4
5
6
7
8
 
SELECT * FROM  T_CON002_INPUT_PAYLOAD
WHERE STATUS IN (1, 2)
ORDER BY id Voici le plan :
Plan
SELECT STATEMENT ALL_ROWSCost: 146 684 Bytes: 50 563 380 Cardinality: 385 980 
2 SORT ORDER BY Cost: 146 684 Bytes: 50 563 380 Cardinality: 385 980 
1 TABLE ACCESS FULL TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 135 364 Bytes: 50 563 380 Cardinality: 385 980

Lorsqu'il y a plus d'un élément dans la liste IN la cardinality calculée sera

(d) card = cardinality à une seule valeur * nombre d'eléments dans la liste IN
card = 192990 * 2 = 385980

et ainsi de suite, la formule ci-dessus continuera à s'appliquer jusqu'à ce que le nombre d'éléments excède le nombre de valeurs distintes de la colonne statut.

Si vous êtes en 8i, il existe un bug dans le CBO lors de l'utilisation d'un IN avec une liste à cause de la transformation en OR. Pour cela il suffit d'utiliser le hint /*+ use_concat */ et le CBO va utiliser les fameuses UNION ALL.

Si vous êtes dans une version plus récente, il va falloir poster l'explain plan avec sa partie predicate et avec les informations E-Rows et A-Rows