Syntax
GENERATOR(N)
Purpose
GENERATOR() is a built-in function in the table function framework. You can call this function in a table function to return N rows of data. N is a 64-bit integer greater than or equal to 0.
A table function is an SQL function that returns results in the form of a table. Unlike traditional SQL functions that return only scalar values, table functions can return a dataset with multiple rows and columns.
The TABLE(GENERATOR(N)) function does not generate any data, but only generates N consecutive rows. To generate data, you need to add specific expressions, such as constants, variables, functions, and user-defined functions (UDFs), after SELECT. TABLE(GENERATOR(N)) can be used for parallel execution, such as parallel SELECT and parallel DML. However, only one thread is used for reading rows generated by TABLE(GENERATOR(N)). Therefore, the degree of parallelism (DOP) is 1 for the data flow object (DFO) that contains TABLE(GENERATOR(N)).
Examples
The following example uses the GENERATOR() function to generate random numbers that meet the conditions.
obclient> SELECT COUNT(*) FROM TABLE(GENERATOR(100000));
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set
obclient> SELECT NORMAL(0, 1, RANDOM()) FROM TABLE(GENERATOR(5));
+------------------------+
| NORMAL(0, 1, RANDOM()) |
|------------------------|
| 0.227384164 |
| 0.9945290748 |
| -0.2045078571 |
| -1.594607893 |
| -0.8213296842 |
+------------------------+
5 rows in set
obclient> SELECT RANDSTR(1, ZIPF(1, 5, RANDOM())) str FROM TABLE(GENERATOR(5));
+------------------------+
| str |
|------------------------|
| A |
| D |
| A |
| A |
| C |
+------------------------+
5 rows in set
You can join the table returned by TABLE(GENERATOR()) with another table. Here is an example:
obclient> CREATE TABLE t1 (c1 BIGINT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient> SELECT c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------+----------------------+
| C1 | RANDOM(1) |
+------+----------------------+
| 1 | -6753783847308464280 |
| 2 | -6707106347154343346 |
| 1 | -899926183391115878 |
| 2 | -8835543475904200562 |
| 1 | -2750444335953844424 |
| 2 | 7588216632478230601 |
+------+----------------------+
6 rows in set
obclient> EXPLAIN SELECT c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------+
| ================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------ |
| |0 |NESTED-LOOP JOIN CARTESIAN | |398 |14 | |
| |1 |├─FUNCTION_TABLE |FUNC_TABLE1|199 |1 | |
| |2 |└─MATERIAL | |2 |2 | |
| |3 | └─TABLE FULL SCAN |t1 |2 |2 | |
| ================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [RANDOM(1)]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output(nil), filter(nil) |
| value(GENERATOR(3)) |
| 2 - output([t1.c1]), filter(nil), rowset=256 |
| 3 - output([t1.c1]), filter(nil), rowset=256 |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------+
19 rows in set
obclient> SELECT /*+ PARALLEL(2) */ c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------+----------------------+
| C1 | RANDOM(1) |
+------+----------------------+
| 1 | -6753783847308464280 |
| 2 | -6707106347154343346 |
| 1 | -899926183391115878 |
| 2 | -8835543475904200562 |
| 1 | -2750444335953844424 |
| 2 | 7588216632478230601 |
+------+----------------------+
6 rows in set
obclient> EXPLAIN SELECT /*+ PARALLEL(2) */ c1, RANDOM(1) FROM t1, TABLE(GENERATOR(3));
+------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------+
| ================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------ |
| |0 |NESTED-LOOP JOIN CARTESIAN | |398 |13 | |
| |1 |├─FUNCTION_TABLE |FUNC_TABLE1|199 |1 | |
| |2 |└─MATERIAL | |2 |2 | |
| |3 | └─PX COORDINATOR | |2 |2 | |
| |4 | └─EXCHANGE OUT DISTR |:EX10000 |2 |2 | |
| |5 | └─PX BLOCK ITERATOR | |2 |1 | |
| |6 | └─TABLE FULL SCAN |t1 |2 |1 | |
| ================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1], [RANDOM(1)]), filter(nil), rowset=256 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output(nil), filter(nil) |
| value(GENERATOR(3)) |
| 2 - output([t1.c1]), filter(nil), rowset=256 |
| 3 - output([t1.c1]), filter(nil), rowset=256 |
| 4 - output([t1.c1]), filter(nil), rowset=256 |
| dop=2 |
| 5 - output([t1.c1]), filter(nil), rowset=256 |
| 6 - output([t1.c1]), filter(nil), rowset=256 |
| access([t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------+
26 rows in set