Le blog de Pacman

Dans l'exemple type des row generators (par exemple ici), on utilise parfois une construction étrange qui est "CONNECT BY level <= N".

C'est certes étrange et abusé, mais ça se conçoit : "connecte moi avec n'importe quoi et récursivement, mais pas plus loin que le 3ème étage".

Cela dit, il y a des moments où on veut générer N lignes pour chaque lignes appartenant à une sélection (de plus d'une ligne cette fois).

CREATE TABLE test_rowgen AS                

SELECT 'pacmann' c, 3 n FROM DUAL UNION ALL

SELECT 'robert' c, 2 n FROM DUAL UNION ALL 

SELECT 'squelettor' c, 1 n FROM DUAL       

Par exemple ici, on voudrait générer 3 lignes pacmann, deux lignes robert, et 1 ligne squelettor.

Et là, si on tente : 

SELECT *             

FROM test_rowgen     

CONNECT BY level <= n

... on obtient toutes les combinaisons possibles récursivement, tant que le niveau est inférieur à "n", ce qui n'est pas bon. La différence avec le générateur à partir de dual, c'est que la ligne de dual ne peut se connecter qu'avec elle-même, alors que là, on a une tripotée d'autres arbres distincts à générer combinatoirement.

Si, pour empêcher de connecter n'importe quoi, on tente de lui préciser qu'il doit se connecter qu'avec lui-même... 

SELECT c, n          

FROM test_rowgen     

CONNECT BY level <= n

    AND c = prior c  

... on obtient : 

ORA-01436: CONNECT BY loop in user data

La boucle est détectée au niveau de l'analyse de la requête, et non au niveau de l'exécution (pour preuve la première requête, une autre preuve un peu plus loin dans cet article)

Si on ajoute NOCYCLE, on ne va pas bien loin : 

SELECT c, n                  

FROM test_rowgen             

CONNECT BY NOCYCLE level <= n

    AND c = prior c          

C                   N

---------- ----------

pacmann             3

robert              2

squelettor          1

Bien entendu, NOCYCLE évite l'erreur, mais pour autant, lorsque le cycle est détecté, aucune connexion n'est tentée pour les chemins suspectés d'induire des cycles.

Pour cela, des petits génies (dont Laurent Schneider, mais lui seul pourra nous dire s'il l'avait repompé ailleurs à l'époque :)) ont hacké le système et ont tenté : 

SELECT c, n                         

FROM test_rowgen                    

CONNECT BY level <= n               

    AND c = prior c                 

    AND prior sys_guid() IS NOT NULL

C                   N

---------- ----------

pacmann             3

pacmann             3

pacmann             3

robert              2

robert              2

squelettor          1

Alors oui, ça marche... mais qu'est ce que ça veut dire ?

Testons de réécrire un peu la requête : 

WITH u AS (                  

    SELECT c, n, sys_guid() t

    FROM test_rowgen         

    )                        

SELECT *                     

FROM  u                      

CONNECT BY level <= n        

    AND c = prior c          

    AND prior t IS NOT NULL  

C                   N T                               

---------- ---------- --------------------------------

pacmann             3 ED2ED377E71B437AE0436203650A0428

pacmann             3 ED2ED377E71D437AE0436203650A0428

pacmann             3 ED2ED377E71F437AE0436203650A0428

robert              2 ED2ED377E721437AE0436203650A0428

robert              2 ED2ED377E723437AE0436203650A0428

squelettor          1 ED2ED377E725437AE0436203650A0428

Ca marche !

Mais si on force la matérialisation : 

WITH u AS (                                  

    SELECT /*+materialize*/c, n, sys_guid() t

    FROM test_rowgen                         

    )                                        

SELECT *                                     

FROM  u                                      

CONNECT BY level <= n                        

    AND c = prior c                          

    AND prior t IS NOT NULL                  

... Paf !

ORA-01436: CONNECT BY loop in user data

Et moi, les résultats qui varient en fonction du plan d'exécution... hum. (Je dis ça, mais faudrait encore vérifier dans quel ordre les choses se passent...)

Par ailleurs la condition "prior sys_guid() is not null" n'empêche en rien les loop...

SELECT c, n                                           

FROM test_rowgen                                 

CONNECT BY prior sys_guid() IS NOT NULL

... boucle infinie tuée à la main après plusieurs millions de lignes fetched.

Donc voilà, cette astuce est démoniaque et plutôt dangereuse.

Je propose donc une méthode encore plus nulle, parce que je le vaux bien : 

SELECT c,n                                                                     

FROM (                                                                         

    SELECT c, n, coalesce(sum(n-1)                                             

    over(order by c desc rows between unbounded preceding and 1 preceding),0) p

    FROM test_rowgen                                                           

    )                                                                          

CONNECT BY rownum <= n + p                                                     

C                   N

---------- ----------

squelettor          1

robert              2

pacmann             3

pacmann             3

robert              2

pacmann             3

Pourquoi est-ce que ça marche ?

Parce qu'au moment de connecter, on se base sur rownum pour estimer le nombre de connexion qui ont été faites sur les lignes précédentes, et en déduire combien il faut en faire la ligne présente.

Pourquoi est-ce que c'est lamentable et à ne surtout pas refaire ?

Parce que d'une part, je n'ai pas la moindre idée de ce qu'est sensé être l'exécution de CONNECT BY, et (surtout ?) parce qu'on ne programme pas en fonction de l'algorithme interne d'Oracle, qui peut très bien changer d'un patch à l'autre (l'introduction du hash group by en étant certainement le plus bel exemple)

Comme dans tout article nihiliste, il faut finir par la clause MODEL (qui marche, n'est pas officiellement prohibée, mais vous mettra à dos vos collègues et votre hiérarchie) : 

SELECT c, n                                     

FROM test_rowgen                                

MODEL                                           

PARTITION BY (c c0)                             

DIMENSION BY (1 i)                              

MEASURES (c,n)                                  

RULES (                                         

    c[FOR i FROM 1 TO n[1] INCREMENT 1] = c[1]  

    , n[FOR i FROM 1 TO n[1] INCREMENT 1] = n[1]

    )                                           

C                   N

---------- ----------

robert              2

squelettor          1

robert              2

pacmann             3

pacmann             3

pacmann             3

C'est bien la première fois que je trouve MODEL moins obscure qu'une quelconque autre méthode...

Ma grand-mère me disait toujours, "Pacman, n'oublie jamais le côté artistique du SQL, ni le côté obscur de la force".

Et là, force est de constater que depuis un moment, je m'y crois trop : j'ai oublié les valeurs essentielles dans lesquelles mes ancêtres et moi croyons, et dans lesquelles ma propre descendance devra croire : l'absurdité, l'inutilité, la créativité non applicable à la vraie vie.

On va donc aborder aujourd'hui : la division relationnelle avec l'opérateur BITAND !

Pour un vrai article sérieux voire boursoufflé sur la division relationnelle, cherchez sur le net.

Par exemple, l'article de SQLPro (vous savez, le mec qui, quand vous lui posez une question sur le forum developpez.net, commence par vous insulter, puis vous recommande d'acheter ses bouquins...), dont nous allons d'ailleurs reprendre le script de tests : 

http://sqlpro.developpez.com/cours/divrelationnelle/

1) La division relationnelle

Il s'agit de repérer dans une table un ensemble de ligne. 

C'est comme faire un filtre where, mais sur tout un groupe dont les lignes doivent matcher, partiellement ou complètement, un certain nombre de valeurs.

Ces valeurs cibles étant par exemple répertoriées dans une autre table.

2) Jeu de données

Comme dit, c'est repompé de chez SQLPro. Il s'agit de dépôts (identifiés par leur ville) qui servent des rayons.

Lesquels sont capables de déservir tous les rayons ?

/* table des rayons */                                                          

CREATE TABLE T_RAYON                                                            

(RAYON_RYN  CHAR(16));                                                          

/* tables des entrepôts */                                                      

CREATE TABLE T_ENTREPOT                                                         

(VILLE_ETP  CHAR(16),                                                           

RAYON_RYN  CHAR(16));                                                           

/* alimentation de la table des rayons */                                       

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('frais');                               

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('boisson');                             

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('conserve');                            

INSERT INTO T_RAYON (RAYON_RYN) VALUES ('droguerie');                           

/* alimentation de la table des entrepots */                                    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'conserve');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'boisson');       

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'conserve');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'droguerie');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boisson');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'frais');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'conserve'); 

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'droguerie');

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'boisson');      

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'frais');        

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'droguerie');    

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'boisson');   

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'frais');     

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'conserve');  

INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'droguerie'); 

/* modif de l'état initial */                                                   

INSERT INTO T_RAYON VALUES ('conserve');                                        

3) La méthode qui perd

La représentation binaire, c'est comme la représentation "usuelle" décimale, sauf qu'au lieu d'avoir des nombres composés de chiffres de 0 à 9, on a des nombres composés de 0 et de 1.

Par exemple, si on prend le nombre décimal 153, c'est : 

- En décimal 1 * 10^2 + 5 * 10^1 + 3 * 10^0 = 153 [decimal]

- En binaire 1 * 2^7 + 0 * 2^6 + 0 * 2^5 + 1 * 2^4 + 1 * 2^2 + 1 * 2^0 = 100111 [binaire]

Pour deux nombres donnés n1 et n2, bitand(n1, n2) est le nombre résultant de l'opération "pour chaque position, si le chiffre correspondant dans n1 et dans n2 vaut 1, on renvoie 1, sinon 0"

Par exemple, 

bitand(153 [décimal], 15 [décimal])

= bitand (100111[binaire], 001111[binaire])

= 000111 [décimal]

= 7

Cette représentation colle parfaitement à une liste de flags : si on affecte à : 

- frais la valeur 1

- boisson la valeur 2

- conserve la valeur 4

- droguerie la valeur 8

... et qu'on fait la somme, on obtient 15, qui s'écrit 1111 en binaire.

Si ensuite on somme ces valeurs pour chaque dépôt, on pourra chercher quels sont les dépôt de somme n2, tels que bitand(n2, 15) = 15.

En SQL, ça donne : 

WITH t AS (                                                                       

SELECT distinct rayon_ryn, power(2, dense_rank() over(order by rayon_ryn) - 1) flg

FROM t_rayon)                                                                     

, u as (                                                                          

SELECT sum(flg) sumflg                                                            

FROM t)                                                                           

SELECT ville_etp                                                                  

FROM t_entrepot a                                                                 

  JOIN t ON a.rayon_ryn = t.rayon_ryn                                             

  CROSS JOIN U                                                                    

GROUP BY ville_etp, sumflg                                                        

HAVING bitand(sum(distinct t.flg), sumflg) = sumflg                               

Voilà voilà, ne pas appliquer ce genre de méthode au boulot, bien entendu...

Quand on veut savoir pourquoi une procédure rame, on peut par exemple la tracer.

"alter session set events 10046 trace name context forever, level 8" est en ce sens un outil efficace, car il permet de déterminer ce qui a fait attendre l'exécution.

C'est donc ce que j'ai fait l'autre jour, et voilà le résultat : 

INSERT INTO MA_TABLE

VALUES (...)

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  47914     60.45     573.76      93619      11588    1221462       47914

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    47915     60.45     573.76      93619      11588    1221462       47914

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 189     (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                     93619        0.83        517.81

  latch free                                      1        0.00          0.00

  latch: shared pool                              1        0.00          0.00

  buffer busy waits                               1        0.00          0.00

  undo segment extension                          1        0.00          0.00

  latch: object queue header operation            2        0.00          0.00

********************************************************************************

On a donc attendu environ 9 minutes en lecture d'index pour insérer 47K lignes.

C'est bien beau tout ça, mais quel(s) index ? La répartition du temps d'attente est homogène ?

Quand on regarde dans le fichier trace brut, on voit plein de lignes de ce genre : 

WAIT #78: nam='db file sequential read' ela= 8907 file#=26 block#=1865617 blocks=1 obj#=1717754 tim=1352309684636336

Et l'obj#, quand on vérifie dans dba_objects, c'est MA_TABLE_FK_ART.

On va donc travailler sur cette lueur d'espoir.

A. Tout d'abord, extraire les waits : 

grep '^WAIT' mon_fichier_trace.trc > trc_dir/my_trc_file.trc

B. Ensuite, créer une table externe pour lire ce fichier en SQL (des compétences correctes en shell Unix m'auraient permis de parser tout ça en live)

CREATE DIRECTORY trc_dir AS '/home/oracle/trc_dir';

CREATE TABLE trc_load  

      (msg varchar2(255))

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY trc_dir

      ACCESS PARAMETERS

        (RECORDS DELIMITED BY NEWLINE

         FIELDS (msg char(255))

        )

      LOCATION ('my_trc_file.trc')

      );  

C. L'analyse de la trace

select object_name

  , regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*') evt

  , sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 totelaela 

  , count(*) cnt

from trc_load a

  join dba_objects b on regexp_substr(regexp_substr(msg, 'obj#=([[:alnum:]]+) tim'), '[[:digit:]]+') = b.object_id

group by regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*'), object_name  

order by sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 desc     

A présent, on peut enfin comprendre et envisager des solutions : 

- Cette table consolide pour une occurence donnée, la photo du stock d'un magasin. 

L'index PK est ordonné ainsi (article, courleur, taille, photo, magasin). Ce qui veut donc dire que le clustering factor est particulièrement mauvais, puisque les insertions se font par le module qui rame, par photo sur un magasin.

=> En réordonnançant la PK, (photo, magasin, article, courleur, taille), il n'y a plus aucune attente pour cet index

- L'index FK article est extrêmement redondante, puisque le nombre de références distinctes est finalement relavitement restreinte, mais dupliquées pour chaque photo de chaque magasin.

=> La compression de l'index divise sa taille part 3, réduisant également le nombre de blocs lus et mis à jour.

Il y a des fois, on agit avant de penser.

Au moment même où je validais ma requête, j'attendais un "ORA-00918: column ambiguously defined".

Mais ce n'est pas arrivé.

1) Les données de test

Précision : on est en version 10.2.0.4.0

create table testeuh as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

create table testeuh1 as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

create table testeuh2 as

select 'a' ida, 'b' idb, 0 val from dual

2) Ce qu'il nous arrive en général

Quand dans une requête sur plusieurs tables, on ne préfixe pas par l'alias de la table une colonne présente dans plusieurs tables, cela donne : 

SELECT val

FROM testeuh2 a

JOIN testeuh b on a.ida = b.id;

SELECT val

       *

ERROR at line 1:

ORA-00918: column ambiguously defined

Normal, car comment savoir quelle colonne val doit être renvoyée ?

Il faut donc préfixer les colonnes avec l'alias de la table : 

SELECT a.val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id;

       VAL

----------

         0

3) Parce que plus on est de fous, plus on rit

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id;

       VAL

----------

         2

=> Ce coup-ci, la requête ne plante pas, et Oracle me renvoie arbitrairement le val de la table testeuh1... comment est-il choisi ?

On ne peut pas affirmer grand chose, mais on peut faire joujou : 

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

       VAL

----------

         1

=> Quand on rajoute une jointure de plus, ça ne plante toujours pas. De plus, c'est la dernière table citée dans la clause FROM qui remporte la mise.

Est-ce que cela dépend du plan d'exécution ?

SELECT /*+leading(b a d c)*/val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

       VAL

----------

         1

Elapsed: 00:00:00.01

Execution Plan

----------------------------------------------------------

Plan hash value: 162124344

---------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  1 |  HASH JOIN           |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  2 |   HASH JOIN          |          |     1 |    37 |    10  (10)| 00:00:01 |

|*  3 |    HASH JOIN         |          |     1 |    18 |     7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| TESTEUH  |     2 |    12 |     3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| TESTEUH2 |     1 |    12 |     3   (0)| 00:00:01 |

|   6 |    TABLE ACCESS FULL | TESTEUH1 |     2 |    38 |     3   (0)| 00:00:01 |

|   7 |   TABLE ACCESS FULL  | TESTEUH1 |     2 |    12 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("A"."IDB"="C"."ID")

   2 - access("A"."IDA"="D"."ID")

   3 - access("A"."IDA"="B"."ID")

=> Et ben non, la table c est accédée en dernier, mais pour autant val ne vaut pas 2.

4) Conclusions

a. C'est une bonne pratique de préfixer systématiquement les colonnes (et en plus ça apporte énormément de lisibilité pour toute personne qui lit le code sans connaître le modèle par coeur)

b. "Ambiguously" provient probablement du préfixe latin amb-, qui porte l'idée de double selon wiktionary.org. Donc à partir de 3, on n'est plus ambigu, et il ne s'agit donc pas d'un bug

L'utilisateur : "Salut, je voudrais un filtre à sélection multiple sur les pays  pour mon rapport XYZ... idéalement avec des cases à cocher" Notez que ce n'est pas un rapport XXX, on fait pas ça chez nous.

Moi :            "Désolé, notre framework est pourri, ça va pas être jouable. Par contre si ça te dit, je te laisse saisir ta sélection, par exemple sous forme de liste de code pays sur 2 caractères, séparés par des virgules. En bonus, un bouton pour afficher la liste des correspondances libellé / code pays."

L'utilisateur : "Vendu !"

Donc je me mets à transformer des chaînes de caractères représentant des listes en lignes, afin de pouvoir faire des filtres "IN (SELECT ...)"

Sujet classique, juste l'occasion de passer en revue les méthodes, et "expliquer" mes choix.

Spliter une chaîne de caractères, ça se fait de manière itérative.

1) Implémenter sa boucle avec CONNECT BY level

Toujours la même astuce : CONNECT BY level sur une sélection d'une seule ligne, ça génère des lignes à la volée, et elles sont "indexables" par level ou rownum : 

define p_str = 'FR,DE,PL,BE'

SELECT substr(str,  instr(str, del, 1, level) + 1, instr(str, del, 1, level+1) - instr(str, del, 1, level) - 1)

FROM (select '&p_str' p_str, ',' || '&p_str' || ',' str, ',' del from dual)

CONNECT BY level <= length(p_str) - length(replace(p_str, del, '')) + 1; 

SUBSTR(STR,IN

-------------

FR

DE

PL

BE

On rajoute un délimiteur en début en fin de liste (mais on pourrait aussi s'embourber dans plein de CASE pour traiter le début et la fin de liste), puis on peut extraire avec substr en s'appuyant sur la position des délimiteurs dans la chaîne.

A partir de 10g, on a la version bien plus élégante avec REGEXP_SUBSTR : 

SELECT regexp_substr('&p_str',  '[^,]+', 1, level)

FROM dual

CONNECT BY level <= length(regexp_replace('&p_str', '[^,]+')) + 1; 

La regexp [^,]+ matche toutes les occurences multiples de caractères qui sont différents de ','... les deux derniers paramètres sont équivalents à INSR, c'est à dire position de départ pour la recherche, et numéro d'occurence à traiter.

Pourquoi je n'ai pas choisi cette solution ?

- Parce que CONNECT BY level, c'est du bugusing (CONNECT BY est sensé faire des arbres, pas du café...)

- Parce que ça fait des mochetés à recoder à chaque fois, et que ça n'arrange pas mes collègues non autistes du SQL 

2) Implémenter sa boucle avec MODEL

A présent, on reprend l'extraction regexp_substr parce que c'est joli, mais on itère avec la clause MODEL

SELECT tk 

FROM (

  SELECT 1 n, p_str, p_str tk, length(regexp_replace(p_str, '[^,]+')) + 1 n_item

  FROM (SELECT  '&p_str' p_str FROM dual) t

)

MODEL 

DIMENSION BY (n)

MEASURES (p_str, tk, n_item)

RULES (

tk[FOR n FROM 1 TO n_item[1] INCREMENT 1] = regexp_substr(p_str[1],  '[^,]+', 1, cv(n))

);

Cependant, je me demande si utiliser la clause model dans du code de production ne pourrait pas être un motif valable de licenciement...

Y a un gars (qui devrait se reconnaître s'il passe par là), qui disait : "As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, ..."

3) XML Table

On peut faire plein de trucs assez loufoques avec XMLTable sous Oracle.

Notamment tout simplement : 

SELECT *

FROM xmltable('&p_str');

C'est tout simple, tout compact, mais j'ai décidé de ne pas l'utiliser parce qu'il y a "XML" dans l'instruction. (Oui oui, c'est purement psychologique)

4) Pipelined function

Une fonction pl/sql, ça sait très bien faire des boucles, et ça sait aussi renvoyer des tableaux.

Et puis, ça peut aussi aussi retourner des lignes au fil de l'eau en étant "pipelined".

C'est tout mignon, on a l'impression de cracher des petites aiguilles empoisonnées avec une sarbacane : 

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF varchar2(4000);

/

CREATE OR REPLACE FUNCTION split_string2(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

PIPELINED

IS

n_item NUMBER;

   s_str VARCHAR2(32767);

   s_ret VARCHAR2(4000);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

FOR i IN 1..n_item LOOP    

s_ret := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

    pipe row(s_ret);

END LOOP;

END split_string2;

/

SELECT *

FROM table(split_string('&p_str',','))

Cela dit, bien qu'étant un grand fan de pipes, je trouve ça stupide de context switch n fois alors que l'exécution de la boucle en elle-même prend un temps négligeable.

5) Function

Bon ben voilà, j'ai choisi au final de faire une petite fonction toute simple (j'ai cependant gardé la méthode d'extraction pourrie à base de substr/instr), qui renvoit tout un tableau : 

CREATE OR REPLACE FUNCTION split_string(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

IS

t_ret varchar2_ntt;

n_item NUMBER;

   s_str VARCHAR2(32767);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

t_ret := varchar2_ntt();

  t_ret.extend(n_item);

FOR i IN 1..n_item LOOP    

t_ret(i) := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

END LOOP;

RETURN t_ret;

END split_string;

/

SELECT *

FROM table(split_string2('&p_str',','))

Il y a des gens qui vous diront qu'appeler des fonctions PL/SQL dans du SQL c'est mal parce que ça context switch.

Mais ici, dans une requête comme celle-ci, la fonction est à priori appelée une seule fois, donc sincèrement, je m'en cogne.

SELECT *

FROM ma_table 

WHERE code_pays IN 

  (SELECT column_value

    FROM table(split_string2('&p_str',',')))