Un truc que j’utilise très souvent pour générer un jeu de données est la requête suivante :

select rownum from dual connect by level<=10000;

Cet ordre ramène 10000 lignes et vous pouvez ainsi générer ce qui vous intéresse. Sous la forme d’un INSERT (SELECT) et avec quelques fonctions MOD et RPAD, cette syntaxe permet d’éviter d’écrire du PL/SQL pour générer vos exemples. Si vous lisez ce blog, cette syntaxe ne vous est pas inconnue !

Pourtant cette méthode a ses limites comme vous pouvez vous en apercevoir ci-dessous :

select count(*) from dual connect by level <= 10000000;
                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

Note
Il existe un bug (11805372) qui ressemble à cette erreur en 11.2.0.2 mais ce n’est pas le problème ici !

Cela est dû au fait que le connect by utilise de la mémoire qu’Oracle ne peut pas descendre sur un segment temporaire. Augmenter la valeur de pga_aggregate_target n’a, en l’occurrence, pas d’effet puisque, par défaut chaque session est limitée à 200M de PGA et que cette limite est atteinte dans ce cas.

Bien sur, il est possible (j’adore Oracle !) d’augmenter cette limite à l’aide de la méthode décrite dans la note : « How To Super-Size Work Area Memory Size Used By Sessions? [ID 453540.1] ».

Je vous laisse regarder ! En passant la limite à 1Go par session qui est le maximum pour un système 32bits, le SELECT fonctionne, comme vous vous en rendrez compte :

SQL> select count(*) from dual connect by level <= 10000000;

  COUNT(*)
----------
  10000000

Lisez bien la note, vous y découvrirez encore quelquechose de nouveau à propos de 11.2.0.2. Et pour finir de vous convaincre de ce qu’il s’est passé, regardez les statistiques sur le PGA de votre session :

select name, value
    from v$mystat m, v$statname n
   where m.statistic#=n.statistic#
     and n.name = 'session pga memory max'
   order by 2;

NAME                       VALUE
---------------------- ----------
session pga memory max  303538864

Il n’est pas vraiment recommandé de jouer avec ce type de paramètre caché. Pourtant cette méthode m’a déjà permis dans quelques rares cas de gagner un facteur sur l’ordre des performances d’une requête.