We often need to create tables for testing or testcases, the usual method is to use CTAS (Create Table As Select) on a select from the dual table and the “CONNECT BY ROWNUM < <number_of_rows_we_need>” clause. Looking at the example below we see that we are creating a table with a single column and one hundred tuples, it has the same uniform distribution as the values we will insert correspond to the ROWNUM. We generated one hundred tuples with the values 1,2,3, … 100, and if we took one random row do they all have the same probability to be elected.
-- CREATE TABLE WITH LINEAL-DISTRIBUTION --- CREATE TABLE T AS SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100;
Creating tables with specific distributions is not very common, but they are a very powerful tool if we wish implement testing, especially to play with the histograms of the columns. An example of creating a table with a Gaussian distribution or Gaussian bell curve called.
-- CREATE TABLE WITH GAUSS-DISTRIBUCION ---- CREATE TABLE T AS SELECT CASE WHEN (ROWNUM BETWEEN 1 AND 10) THEN 1 WHEN (ROWNUM BETWEEN 11 AND 30) THEN 2 WHEN (ROWNUM BETWEEN 31 AND 70) THEN 3 WHEN (ROWNUM BETWEEN 71 AND 90) THEN 4 WHEN (ROWNUM BETWEEN 91 AND 100) THEN 5 END X FROM DUAL CONNECT BY ROWNUM <= 100;
In this case we have created a table with one hundred tuples, and has the values 1,2,3,4 and 5 however, if we took a random row we would have the following probabilities.
- The probability of being a value of 1 is 10%
- The probability of being a value of 2 is 20%
- The probability of being a value of 3 is 40%
- The probability of being a value of 4 is 20%
- The probability of being a value of 5 is 10%
HTH – Antonio NAVARRO