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

[tommey]

Le plan demandé :
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

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 ?

1-non
2-oui
3-100
4- 82

Merci!!

[tommey]

Mais ne creusez pas trop vos têtes.

Le union all m'a donné un bon coup de main.

Je travail sur quelque chose...

Merci à tous vous êtes sweet

[tommey]

Ok j'ai besoin de vous une dernière fois.

Voici la requête que je veux faire fonctionner :

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
WITH DATA_PAGINE AS (
 SELECT /*+ MATERIALIZE  */   ID FROM (
                    (SELECT A1.ID,  ROWNUM RNUM
                         FROM  T_CON002_INPUT_PAYLOAD A1,
                                    (SELECT ID
                                          FROM T_CON002_INPUT_PAYLOAD
                                         WHERE STATUS = 1
                                        UNION ALL
                                        SELECT ID
                                          FROM T_CON002_INPUT_PAYLOAD
                                         WHERE STATUS = 2) A2
                           WHERE A1.ID = A2.ID
                            ORDER BY A1.CREATE_DATE) 
                       TBL_IDS) WHERE RNUM BETWEEN 200 AND 300) 
SELECT t1.*
  FROM T_CON002_INPUT_PAYLOAD T1
  JOIN DATA_PAGINE T2 ON T1.ID = T2.ID
ORDER BY CREATE_DATE

Voici le plan d'exécution :

Plan
SELECT STATEMENT ALL_ROWSCost: 165 972 Bytes: 52 879 260 Cardinality: 385 980
25 TEMP TABLE TRANSFORMATION
19 LOAD AS SELECT SYS_TEMP_0FD9D665E_A3E47F6
18 VIEW CON3FEF01. Cost: 11 712 Bytes: 7 333 620 Cardinality: 385 980
17 SORT ORDER BY Cost: 11 712 Bytes: 7 719 600 Cardinality: 385 980
16 COUNT
15 HASH JOIN Cost: 9 359 Bytes: 7 719 600 Cardinality: 385 980
10 VIEW CON3FEF01. Cost: 743 Bytes: 2 315 880 Cardinality: 385 980
9 UNION-ALL
4 VIEW VIEW CON3FEF01.index$_join$_004 Cost: 3 731 Bytes: 1 736 910 Cardinality: 192 990
3 HASH JOIN
1 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 363 Bytes: 1 736 910 Cardinality: 192 990
2 INDEX FAST FULL SCAN INDEX (UNIQUE) CON3FEF01.PK_CON002 Cost: 2 315 Bytes: 1 736 910 Cardinality: 192 990
8 VIEW VIEW CON3FEF01.index$_join$_005 Cost: 3 731 Bytes: 1 736 910 Cardinality: 192 990
7 HASH JOIN
5 INDEX RANGE SCAN INDEX CON3FEF01.IX_TEST Cost: 363 Bytes: 1 736 910 Cardinality: 192 990
6 INDEX FAST FULL SCAN INDEX (UNIQUE) CON3FEF01.PK_CON002 Cost: 2 315 Bytes: 1 736 910 Cardinality: 192 990
14 VIEW VIEW CON3FEF01.index$_join$_002 Cost: 7 088 Bytes: 13 509 286 Cardinality: 964 949
13 HASH JOIN
11 INDEX FAST FULL SCAN INDEX CON3FEF01.IX_TEST2 Cost: 3 215 Bytes: 13 509 286 Cardinality: 964 949
12 INDEX FAST FULL SCAN INDEX (UNIQUE) CON3FEF01.PK_CON002 Cost: 2 315 Bytes: 13 509 286 Cardinality: 964 949
24 SORT ORDER BY Cost: 154 260 Bytes: 52 879 260 Cardinality: 385 980
23 HASH JOIN Cost: 142 502 Bytes: 52 879 260 Cardinality: 385 980
21 VIEW CON3FEF01. Cost: 274 Bytes: 2 315 880 Cardinality: 385 980
20 TABLE ACCESS FULL TABLE (TEMP) SYS.SYS_TEMP_0FD9D665E_A3E47F6 Cost: 274 Bytes: 2 315 880 Cardinality: 385 980
22 TABLE ACCESS FULL TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 135 362 Bytes: 126 408 319 Cardinality: 964 949


Le problème est que le select du bas utilise un table access full au lieu de passer par son index (pk).

J'ai fais un test, je me suis créé une global temporary table et je lui insère le contenu de mon WITH (ce qui prend 1 seconde) et je refais mon select du bas mais en faisant la jointure sur la table temporaire. Voici le code :

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
CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE (ID INTEGER)
 
INSERT INTO TEMP_TABLE (
SELECT  ID FROM (
                    (SELECT A1.ID,  ROWNUM RNUM
                         FROM  T_CON002_INPUT_PAYLOAD A1,
                                    (SELECT ID
                                          FROM T_CON002_INPUT_PAYLOAD
                                         WHERE STATUS = 1
                                        UNION ALL
                                        SELECT ID
                                          FROM T_CON002_INPUT_PAYLOAD
                                         WHERE STATUS = 2) A2
                           WHERE A1.ID = A2.ID
                            ORDER BY A1.CREATE_DATE) 
                       TBL_IDS) WHERE RNUM BETWEEN 200 AND 300
)
 
SELECT t1.*
  FROM T_CON002_INPUT_PAYLOAD T1
  JOIN TEMP_TABLE T2 ON T1.ID = T2.ID
ORDER BY CREATE_DATE

Le plan :

Plan
SELECT STATEMENT ALL_ROWSCost: 205 Bytes: 14 544 Cardinality: 101
6 SORT ORDER BY Cost: 205 Bytes: 14 544 Cardinality: 101
5 NESTED LOOPS
3 NESTED LOOPS Cost: 204 Bytes: 14 544 Cardinality: 101
1 TABLE ACCESS FULL TABLE (TEMP) CON3FEF01.TEMP_TABLE Cost: 2 Bytes: 1 313 Cardinality: 101
2 INDEX UNIQUE SCAN INDEX (UNIQUE) CON3FEF01.PK_CON002 Cost: 1 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE CON3FEF01.T_CON002_INPUT_PAYLOAD Cost: 2 Bytes: 131 Cardinality: 1


On voit que la récupération dans la table T_CON002_INPUT_PAYLOAD utilise l'index PK_CON002.

J'aimerais savoir pourquoi ce n'est pas le cas dans mon premier select?

Est-ce parce que mon with (qui est matérialisé) n'est pas physique?

Je vois que la première jointure se fait en HASH JOIN mais la deuxième est une nested loop.


Quelqu'un a une idée?

Si je réussi à régler ça mon problème est réglé. Ou sinon je vais carément utiliser une temporary table...

Merci!

[tommey]

Plus facile encore...

Admettons une table avec une colonne et des valeurs de 1 à 100.

Code :

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

1
2
3
WITH DATA1 AS (SELECT VALEUR FROM TABLE_1 WHERE ROWNUM < 10)
SELECT  T1.* FROM TABLE_1 T1
WHERE EXISTS (SELECT 1 FROM DATA1 T2 WHERE  T1.VALEUR = T2.VALEUR)

Pourquoi si je cré une vue matérialisé avec le select DATA1 et que je le join à table_1, il retrouvera les bons enregistrements avec l'index et une nested loop mais qu'avec le with, il n'utilise pas l'index et utilise un hash join?

[Waldar]

Ça dépend de la répartition de vos données. Quand vous récupérez le status 2, c'est quasiment 30% de votre table. Oracle décide qu'il est plus rapide de lire toute la table en full scan plutôt que de lire l'index, récupérer le rowid puis lire la table.

Ne pas utiliser l'index n'est pas forcément signe de mauvaise performance.
De plus je vois dans vos requêtes que vous avez implémenté de la pagination, vous n'êtes pas tout-à-fait dans les bonnes pratiques.

Parcourez le sujet suivant (en anglais) : http://asktom.oracle.com/pls/apex/f?...D:127412348064

[tommey]

Ok j'ai trouvé mon problème en modifiant la pagination

Merci à tous