Examples to generate large datasets in Oracle, PostgreSQL and MySQL

 In Oracle:

create table t_test (id integer generated as identity, name varchar2(20));

insert into t_test (name) select 'hans' from dual connect by level <= 2000000;

insert into t_test (name) select 'paul' from dual connect by level <= 2000000;

COMMIT;

CREATE INDEX idx_id ON t_test (id);

SQL> select segment_name,bytes from user_segments where segment_name in ('IDX_ID','T_TEST');

   SEGMENT_NAME       BYTES 

_______________ ___________ 

IDX_ID             83886080 

T_TEST             75497472 

In PostgreSQL:

CREATE TABLE t_test (id serial, name text);

INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);

INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);

CREATE INDEX idx_id ON t_test (id);

mytest=> SELECT pg_size_pretty(pg_relation_size('T_TEST')) AS "T_TEST", 

pg_size_pretty(pg_relation_size('IDX_ID')) AS "IDX_ID";

 T_TEST | IDX_ID 

--------+--------

 169 MB | 86 MB

(1 row)

In MySQL:

CREATE TABLE t_test (id int auto_increment, name varchar(10), primary key 

(id));

set @@cte_max_recursion_depth=100000000;

INSERT INTO t_test (name) 

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT 'hans' FROM cte;

INSERT INTO t_test (name) 

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT 'hans' FROM cte;

------ Redo the testing without depending on MySQL "Primary" index

CREATE TABLE t_test (id int, name varchar(10));

set @@cte_max_recursion_depth=100000000;

INSERT INTO t_test

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT *, 'hans' FROM cte;

INSERT INTO t_test

WITH RECURSIVE cte (n) AS

(

  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 2000000

)

SELECT  *, 'paul' FROM cte;

CREATE INDEX idx_id ON t_test (id);

mysql> SELECT database_name, table_name, index_name, 

ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb FROM mysql.innodb_index_stats 

WHERE stat_name = 'size' AND index_name != 'PRIMARY' and table_name='t_test' ORDER BY size_in_mb DESC;

+---------------+------------+-----------------+------------+

| database_name | table_name | index_name      | size_in_mb |

+---------------+------------+-----------------+------------+

| test2         | t_test     | GEN_CLUST_INDEX |     142.67 |

| test2         | t_test     | idx_id          |      73.61 |

+---------------+------------+-----------------+------------+

2 rows in set (0.00 sec)