Mot clé SQL 'IN'. Comment l'éviter?

**boutade80** · 01/07/2009, 16h18

Bonjour,

Je cherche à éviter l'utilisation du mot clé IN ds mes requetes sql.
Voila pas exemple une des mes requetes que je voudrais modifier:
J'ai un index sur la colonne matricule

Code :

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

1
2
3
4
5
select 
m.nom
from TabMatricule m
where
m.matricule IN ('1895008345','1397104117','8194700690')

Oracle me converti ca en une suite de 'OR' et me lance un full scan table.
Comment eviter ca? Je voudrais que pour chaque matricule recherché, oracle fasse une recherche par index.

La difficulté est dans le fait que je ne connais pas le nombre de matricules que mes applis clients vont m'envoyer. Il est probable que je recoive des traitements avec 2000 matricules en parametre. Je ne peux donc pas faire ca par exemple:

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
select
m.nom
from TabMatricule  m
where
m.matricule IN ('1895008345')
UNION ALL
select
m.nom
from TabMatricule  m
where
m.matricule IN ('1397104117')
UNION ALL
select
m.nom
from TabMatricule  m
where
m.matricule IN ('8194700690')

Avez vous une idée pour résoudre ce problème?

Bien cordialement

X.

**mnitu** · 01/07/2009, 16h43

Envoyé par boutade80

...
J'ai un index sur la colonne matricule

Code :

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

1
2
3
4
5
select 
m.nom
from TabMatricule m
where
m.matricule IN ('1895008345','1397104117','8194700690')

Oracle me converti ca en une suite de 'OR' et me lance un full scan table.
Comment eviter ca? Je voudrais que pour chaque matricule recherché, oracle fasse une recherche par index.

Et pourquoi pensez-vous que cella arrive, parce que ce n’est pas le IN le problème ?

Envoyé par boutade80

...
La difficulté est dans le fait que je ne connais pas le nombre de matricules que mes applis clients vont m'envoyer. Il est probable que je recoive des traitements avec 2000 matricules en parametre. ...
Avez vous une idée pour résoudre ce problème?

Il y en a plusieurs solutions dont voici une

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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0 
Connected as mni
 
SQL> create or replace type vStr as varray(2000) of varchar2(10)
  2  /
 
Type created
 
SQL> 
SQL> Select e.*
  2    From emp e,
  3         table(vStr('7900','7369','7499')) a
  4   Where To_Number(a.column_value) = e.empno
  5  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 03/12/1981     950,00               30
 7369 SMITH      CLERK      7902 17/12/1980     800,00               20
 7499 ALLEN      SALESMAN   7698 20/02/1981    1600,00    300,00     30
 
SQL>

**plaineR** · 01/07/2009, 17h07

Si les statistiques sont correctement calculées, si Oracle décide de faire un full scan, c'est peut-être que c'est plus performant.

Pourquoi vouloir forcément passer par l'index ?

**boutade80** · 01/07/2009, 17h48

Veuillez m'excuser, après m'etre relu je me suis rendu compte que je n'avais pas été très clair.
La table Tabmatricule contient 700 000 lignes
La colonne matricule est indexée

1er cas:

Code :

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

1
2
3
4
5
select 
m.matricule
from Tabmatricule m
where
m.matricule IN ('19500118345','19710420117','19470520690')

methode d'accès: INDEX FAST FULL SCAN
Cout: 491

2ieme cas: (mnitu)

Code :

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

1
2
3
4
5
CREATE OR REPLACE type vStr AS varray(2000) of varchar2(12)
SELECT matricule 
   FROM Tabmatricule e,
        TABLE(vStr('19500118345','19710420117','19470520690')) a
WHERE a.column_value = e.matricule

methode d'accès: INDEX FAST FULL SCAN
Cout : 510

3ieme cas:

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
select
m.matricule 
from Tabmatricule m
where
m.matricule  = '1895008345'
UNION ALL
select
m.matricule 
from Tabmatricule m
where
m.matricule  = '1397104117'
UNION ALL
select
m.matricule 
from Tabmatricule m
where
m.matricule  = '8194700690'

methode d'accès: INDEX RANGE SCAN pour chacune des requetes
Cout: 9

Mon soucis est que les developpeurs m'envoient une chaine de caractères avec les matricules les uns derriere les autres (ex: "'19500118345','19710420117','19470520690'"). je voudrais savoir s'il est possible d'ecrire une requete SQL (comme cas 1 ou 2) ou procédure PL/SQL qui me permettent d'avoir un cout aussi bon que dans le 3ieme cas.

**mnitu** · 01/07/2009, 21h19

1° Pourquoi le INDEX FAST FULL SCAN

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
mni@DIANA> exec dbms_random.seed(0)
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
mni@DIANA>
mni@DIANA> create table TabMatricule as
  2  select to_char(trunc(dbms_random.value(1000000000,9999999999))) matricule,
  3         object_name nom
  4    from all_objects
  5  where rownum < 10000
  6  /
 
Table crÚÚe.
 
mni@DIANA> create index ix_TabMatricule on TabMatricule(matricule)
  2  /
 
Index crÚÚ.
 
mni@DIANA> exec dbms_stats.gather_table_stats(user,'TABMATRICULE',cascade=>true)
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
mni@DIANA>
mni@DIANA> set autotrace on
mni@DIANA>
mni@DIANA> select nom from tabmatricule where matricule in ('4331437079','8456138452','9578100277')
  2  /
 
NOM
------------------------------
C_COBJ#
I_USER1
I_IND1
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1331823828
 
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     3 |    87 |     6   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABMATRICULE    |     3 |    87 |     6   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_TABMATRICULE |     3 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("MATRICULE"='4331437079' OR "MATRICULE"='8456138452' OR
              "MATRICULE"='9578100277')
 
 
Statistiques
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
 
mni@DIANA>
mni@DIANA> select nom from tabmatricule where matricule = '4331437079' union all
  2  select nom from tabmatricule where matricule ='8456138452' union all
  3  select nom from tabmatricule where matricule ='9578100277'
  4  /
 
NOM
------------------------------
C_COBJ#
I_USER1
I_IND1
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 937890806
 
------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     3 |    87 |     6  (67)| 00:00:01 |
|   1 |  UNION-ALL                   |                 |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TABMATRICULE    |     1 |    29 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_TABMATRICULE |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| TABMATRICULE    |     1 |    29 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_TABMATRICULE |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| TABMATRICULE    |     1 |    29 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | IX_TABMATRICULE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("MATRICULE"='4331437079')
   5 - access("MATRICULE"='8456138452')
   7 - access("MATRICULE"='9578100277')
 
 
Statistiques
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        482  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

2° L'exemple que j'ai vous ait fournit montre une solution à votre problème

**NGasparotto** · 01/07/2009, 22h12

Envoyé par boutade80

...La colonne matricule est indexée...

Peut-etre cette colonne n'est pas la seule dans l'index ?
Un matricule ne devrait-il pas etre unique ?
Comment est declare cet index ?

Il serait bien de montrer les explains plan des differentes requetes pour pouvoir avancer.

Nicolas.

**boutade80** · 09/07/2009, 17h06

Je viens de trouver la cause de mon problème.
Merci pour ton exemple mnitu, ca m'a permis de pointer mon erreur.

En fait l'index sur matricule a été crée avec l'option DESC

Code :

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

1
2
3
4
5
6
7
8
"CREATE INDEX ".$schema.".IDX_MAT
					".$schema.".MATRICULE
					(
					  matricule_fk DESC
					)
					NOLOGGING
					NOCOMPRESS
					SORT"

J'ai supprimé le mot clé DESC et ca fonctionne parfaitement. Je vais chercher à comprends pourquoi maintenant.

Merci à vous

Mot clé SQL 'IN'. Comment l'éviter?

SQL Oracle

Discussions similaires

Partager

Partager