Declaration
GENERATOR(N)
Description
This function is a built-in function within the Table Function framework, meaning it can be called within a Table Function and ultimately returns N rows of data. N is a non-negative 64-bit positive integer.
A table function is a function used in the SQL language that returns a table as its result. Unlike traditional SQL functions, which can only return scalar values, table functions can return multi-row, multi-column datasets.
TABLE(GENERATOR(N)) does not actually generate any data; it only produces N consecutive "rows". To generate data, you need to add specific expressions after SELECT, such as constants, variables, functions, or UDFs. TABLE(GENERATOR(N)) can be used for parallel execution, such as parallel SELECT and parallel DML. However, only one thread is responsible for reading the rows generated by TABLE(GENERATOR(N)). This means that a DFO containing TABLE(GENERATOR(N)) has a DOP=1.
Examples
The following example shows how to use the GENERATOR() function to generate a random number that meets the specified 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
The GENERATOR() function can also be used to join other tables. 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