Clause WHERE xyz IN [PARAMETRE] / bonne pratique

Bonjour,

Je débute sous Oracle, je connais bien SQL Server et j'avoue que le passage sur ce nouveau sgbd ne se fait pas en douceur pour moi :).

Je cherche à écrire une procédure stockée avec en paramètre d'entrée une liste du type 'A,B,C'
Je souhaite lister tous les enregistrements d'une table avec une condition sur cette liste

Du style :

Code:

---

SELECT categ from MATABLE WHERE categ IN ('A','B','C')

---

mais avec un paramètre pour la liste.

J'ai pondu ça :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create or replace PROCEDURE X_TEST ( P_LISTE IN VARCHAR2, p_cursor OUT sys_refcursor ) AS BEGIN OPEN p_cursor FOR   select categ from MATABLE WHERE categ IN ( SELECT regexp_substr(P_LISTE,'[^,]+', 1, level) as c FROM dual connect BY regexp_substr(P_LISTE, '[^,]+', 1, level) IS NOT NULL );   END X_TEST;

---

Appel à la procédure :

Code:

---

1 2 3

VARIABLE rc REFCURSOR; execute X_TEST('A,C',:rc); print rc;

---

Cela fonctionne bien mais je souhaite vérifier si il n'existe pas une solution plus simple, si je ne suis pas trop à "coté de la plaque" en terme de bonnes pratiques...

Merci d'avance.

Bonjour,

C'est bien, mais c'est pas top !

En effet lorsque vous faites

Code:

---

1 2 3

select ... from T where T.C in ( select founction() from dual connect by prior(fonction()) ... )

---

L'optimiseur est contraint de calculer la cardinalité (nombre de lignes) à renvoyé par le sql hyérarchique pour savoir le nombre de valeur qu'il y aura dans le 'in' et donc éventuellement estimer le nombre de linges de T filtrées.

Votre code PL/SQL remonte un nombre variable de lignes donc pour un même code SQL vous allez pouvoir remonter de quelques lignes à toute la table T.
A partir de là le plan idéal devrait être différent mais comme vous avez un seul code SQL, vous n'aurez qu'un seul plan car la cardinalité de votre sous select sera estimée à 1 (en version 11.2) quelle que soit la taille de votre liste et a donc de fortes chances de s'orienter vers un parcours par index même si toute la table doit être ramenée.

en résumé, si vous pouvez éviter d'avoir derrière un même code SQL plusieurs résultats différents (en termes de cardinalité seulementà évitez le, même si vous devez écrire plusieurs requêtes. Mieux vaut vous fatiguer une bonne heure maintenant à écrire un code correct plutôt que de se créer des problèmes de performance aléatoires


Au passage un petit TC :

Un schéma pour tester

Code:

---

1 2 3 4 5 6

create table toto as select * from dba_objects ;   create index toto_idx on toto(object_name) ;   exec dbms_stats.gather_table_stats(user,'TOTO') ;

---

La requête :

Code:

---

1 2 3 4

select * from toto where object_name in ( SELECT regexp_substr('A,B,C,D','[^,]+', 1, level) AS c FROM dual connect BY regexp_substr('A,B,C,D', '[^,]+', 1, level) IS NOT NULL)

---

Le plan utilisé :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 6 (100)| | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 2 | 226 | 6 (17)| 00:00:01 | | 3 | VIEW | VW_NSO_1 | 1 | 16 | 3 (34)| 00:00:01 | | 4 | HASH UNIQUE | | 1 | | 3 (34)| 00:00:01 | | 5 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | |* 7 | INDEX RANGE SCAN | TOTO_IDX | 2 | | 2 (0)| 00:00:01 | | 8 | TABLE ACCESS BY INDEX ROWID | TOTO | 2 | 194 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------

---

Moi j'utilise toujours INSTR pour les listes de paramètre

Code:

---

1 2 3 4 5 6 7 8 9

SELECT table_name FROM all_tables WHERE owner = 'SYS' AND INSTR(','|| 'UNDO$,USER$,UET$'||',', ','|| table_name ||',') > 0   TABLE_NAME USER$ UET$ UNDO$

---

@ojo77 : ok pour la non optimisation de ma solution
@McM : ta solution me semble plus propre et beaucoup plus simple que la mienne :) Je pense donc l'adopter.

Merci pour vos réponses.