DBMS_RANDOM, CONNECT BY LEVEL : Alimentation d'une table de test

Lorsque l'on doit effectuer un test, on doit rapidement créer une table de test avec quelques champs simples et des valeurs séquentielles ou aléatoires. Voici des méthodes qui permettent d'atteindre ce but.

1ère méthode

SQL> CREATE TABLE toto(
  2     id  NUMBER,
  3     str VARCHAR2(4000),
  4     num NUMBER
  5  );

Table created.

SQL> BEGIN
  2  FOR i IN 1..200
  3  LOOP
  4  INSERT INTO toto VALUES(i, dbms_random.string('A',12),
  5   trunc(dbms_random.value(1,1000)));
  6  END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

 

2ème méthode

SQL> CREATE TABLE toto AS
  2  SELECT rownum id, dbms_random.string('A', 12) str,
  3  trunc(dbms_random.value(1,1000)) num
  4  FROM all_objects
  5  WHERE rownum <= 200;

 

Résultat (méthodes 1 et 2)

 

SQL> COL STR FOR A12
SQL> SET PAGES 300
SQL> SELECT * FROM toto;

        ID STR                 NUM
---------- ------------ ----------
         1 ebGePFqTEVdt        356
         2 tuFhNYHGyfCq        222
         3 atQzvjgiHrAa        560
         4 YlBAahgGNEeu        828
         5 moJUngYgFmDl         79
         6 kkSTXdSPYhof        734
         7 PupVlDoEEUPf        450
         8 FMgJpmltAEkf        377
         9 DcfSaZOdBJku        821
        10 HFTPIYfDnHBK        521
...
       198 oIsMiAsCUPXo        400
       199 VangiDQjfWHX         42
       200 dobTswbjYOsL        219

200 rows selected.

 

Problème : si l'on veut créer plus de lignes qu'il n'y en a dans la plus grande table du catalogue (dba_segments ou dba_extents ou dba_tables par exemple...) des millions de lignes ?

Une astuce consiste à utiliser un produit Cartésien (habituellement pourchassé par les DBAs à juste titre...)

SQL> CREATE TABLE toto AS
  2  SELECT rownum id, dbms_random.string('A', 12) str,
  3  trunc(dbms_random.value(1,1000)) num
  4  FROM
  5  (SELECT 1 FROM all_objects WHERE rownum <= 9),
  6  (SELECT 1 FROM all_objects WHERE rownum <= 9)
  7  ;

Table created.

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
        81

SQL> SELECT MIN(id), MAX(id), COUNT(*) FROM toto;

   MIN(ID)    MAX(ID)   COUNT(*)
---------- ---------- ----------
         1         81         81

  

Bon allez, tout ça c'était pour vous faire réfléchir, car en fait il existe une méthode encore plus triviale, mais si on ne la connait pas, c'est plus difficile de l'inventer (moi je l'ai trouvée un jour sur le net, comme ça par hasard ...). Cette ultime méthode permet de s'affranchir à la fois de trouver une table assez grande comme source de lignes et de faire une boucle PL.

SQL> CREATE TABLE toto AS
  2  SELECT rownum id, dbms_random.string('A', 12) str,
  3  trunc(dbms_random.value(1,1000)) num
  4  FROM dual
  5  CONNECT BY LEVEL <= 200
  6  ;

Table created.
 
SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
       200

Enfin, une petite astuce pour obtenir une distribution non-uniforme de valeurs (skewed values), dans le cas par exemple de tests sur les histogrammes :
 

SQL> CREATE TABLE toto AS
  2  SELECT 1 id
  3  FROM dual
  4  CONNECT BY LEVEL <= 10000
  5  ;

Table created.

SQL> UPDATE toto
  2  SET id=2
  3  WHERE ROWNUM <= 10
  4  ;

10 rows updated.

SQL> SELECT id, COUNT(*)
  2  FROM toto
  3  GROUP BY id
  4  ;

        ID   COUNT(*)
---------- ----------
         2         10
         1       9990