Discussion :

[kipkip]

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 :

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

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

mais avec un paramètre pour la liste.

J'ai pondu ça :

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
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 :

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

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.

[ojo77]

Bonjour,

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

En effet lorsque vous faites

Code :

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

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 :

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

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 :

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

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 :

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

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 |
------------------------------------------------------------------------------------------------------

[McM]

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

Code :

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

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$

[kipkip]

@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.