How to insert larger number (over 100 million) of dummy records into database table quickly?

Question

For testing purposes I need to insert over 100 million records to a table. What is the best way to do it?

Answer 1

You can use a hierarchical query:

INSERT INTO table_name (id, name, datetime)
SELECT LEVEL,
       'XYZ' || LPAD(LEVEL, 10, '0'),
       DATE '1970-01-01' + INTERVAL '1' SECOND * (LEVEL - 1)
FROM   DUAL
CONNECT BY LEVEL <= 100000000;

or a recursive query:

INSERT INTO table_name (id, name, datetime)
WITH rsqfc (id) AS (
  SELECT 1 FROM DUAL
UNION ALL
  SELECT id + 1 FROM DUAL WHERE id < 100000000
)
SELECT id,
       'XYZ' || LPAD(id, 10, '0'),
       DATE '1970-01-01' + INTERVAL '1' SECOND * (id - 1)
FROM   DUAL;

Answer 2

Use an external tool to generate text file with rows in convenient format to be later imported with SQLLDR in direct mode, you can split the file to run several processes in parallel (parallel=true parameter, version 23c has even a new parameter to import in parallel from 1 invocation). Look also at transportable tablespaces to avoid downtime if you can do the import in a non-live system and attach them later to the final instance.