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

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:

---

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:

---

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.

Citation:

---

Envoyé par boutade80

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

Code:

---

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 ?

Citation:

---

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:

---

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>

---

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 ?

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:

---

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:

---

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:

---

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.

1° Pourquoi le INDEX FAST FULL SCAN

Code:

---

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

Citation:

---

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.

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:

---

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