Author: Jeffrey Kemp

Every database administrator has to generate test data one time or another. In best case the test data must match your existing data. You must have faced the scenario where you have to generate test data a number of times. This test data may include data for tables that mandatory have foreign keys to the existing tables in your database.

In such situations it is not an appropriate approach to create new master rows every time for the test data. You can get back to generate a random sample of existing data but the point is even though your code has to get random values from a very large table but it should still be efficient and should not affect the performance.

In this article we will discuss a number of options that will help you efficiently generate test data.

Code to Generate Test Data that matches existing Data:

Below is an efficient solution that uses Oracle 10g SAMPLE operator and DBMS_RANDOM to generate test data that matches the existing data in your tables.

The basic approach is as under

1. Open the cursor
2. Fetch the number of rows based on the amount of test data needed.
3. Close the cursor

The code efficiently handles the situation where your sample is small as compared to the amount of test data you desire. In such case the cursor will be re-opened and some other set of random values will be fetched from the large table.

Create Table:

First of all we will create a large table.

CREATE TABLE MYTABLE AS SELECT MYROWNUM N, DBMS_RANDOM.STRING('A',30) VAR FROM ALL_OBJECTS;
Table created.

We can see that our table is really large with a total of 51072 number of rows.

SELECT COUNT(*) FROM MYTABLE;

  COUNT(*)
  ------------
  51072

Random Sample:

Now we will obtain a random sample from our table MYTABLE.

SELECT NUM, SUBSTR(VAR,1,33) FROM MYTABLE SAMPLE(0.01) ORDER BY DBMS_RANDOM.VALUE;

  NUM                             SUBSTR(VAR,1,33)
  ------------     --------------------------------------------------------------
   71329         aIjDhezmsdAmWHHPOyKAjFRYTTfJSSCuO
  2491             yxZucJWeAUuDjClvxtjqMfVayKdiKFGv
  210327         xSsdmFtNtVMptosPchILHTxpJAAPwaVaa
  3781             gkFviAIeAXaIhIhsiSCuNRGyNYUpIbCwW
  127               ChiTxUPYJJtoieSWtzsUTIZXCbOLKzWw
  50603           AwMTumPxsfBMoAGqkymAbKCFoZwUzcNXJ
6 rows selected.

Set Server Output:

Now we will set the SERVEROUTPUT parameter to ON.

SQL> SET SERVEROUTPUT ON

Get Value at Random:

Finally we will use below code to get a single value that will be chosen at random.

DECLARE
    CURSOR MYCURSOR IS
        SELECT NUM FROM MYTABLE SAMPLE(0.01)
        ORDER BY DBMS_RANDOM.VALUE;
          MYNUM NUMBER;
          BEGIN
            OPEN MYCURSOR;
            FETCH MYCURSOR INTO MYNUM;
            DBMS_OUTPUT.PUT_LINE(MYNUM);
            CLOSE MYCURSOR;
        * END;
  /
39140
PL/SQL procedure successfully completed.

Oracle Tools for Test Data Generation:

Oracle provides a number of tools that can help you generate test data. Continued...

 More Database Articles
   Database Security: Step by step guideline
   Add Flexibility to your database – Use Database Abstraction Layer
   Oracle Asynchronous COMMIT: Facts and Concerns!
   Great Tips on solving Temporary Tablespace Problems!
   Identify the root causes of Poor Oracle Performance!!