Is there a better query/code that will take less time to insert these data?

Question

I am trying to insert millions of rows into a table as shown in the below code. I want to insert first 3 million rows and then 30 million, 300 million and so on. probably stop at 300 million.

The reason I am doing this is that I want to test the table for partitioning. I want to test the table on a select or other statement when partitioned and not partitioned. I did the first insert (3 million rows) and this took more than 30 minutes.

Is there an easy way to insert the data that will take less time?

declare
  c_id  number := 0 ;
begin        
  while (c_id <= 3000000)
  loop
    INSERT all

      when c_id <= 500000 then
      into city
      values(c_id,'City' || c_id || sysdate,'NY',c_id + 500 || sysdate)

      when c_id between 500001 and 1000000 then
      into city
      values(c_id,'City' || c_id || sysdate,'CA',c_id + 500 || sysdate)

      when  c_id between 1000001 and 1500000 then 
      into city
      values(c_id,'City' || c_id || sysdate,'TX',c_id + 500 || sysdate)

      when c_id between 1500001 and 2000000 then 
      into city
      values(c_id,'City' || c_id || sysdate,'WA',c_id + 500 || sysdate)

      when c_id between 2000001 and 2500000 then
      into city
      values(c_id,'City' || c_id || sysdate,'NC',c_id + 500 || sysdate)

      when c_id > 2500000 then
      into city
      values(c_id,'City' || c_id || sysdate,'SC',c_id + 500 || sysdate)

    select 1 from dual;

      c_id := c_id + 1;
  end loop;
end;

Answer 1

Here is how I would generate the test data:

HUSQVIK@panel_management> CREATE TABLE CITIES (ID NUMBER NOT NULL, CITY VARCHAR2(100) NOT NULL, STATE VARCHAR2(2) NOT NULL, SOME_DATA VARCHAR2(255)) NOLOGGING;

Table created.

Elapsed: 00:00:00.01
HUSQVIK@panel_management> ALTER SESSION FORCE PARALLEL DML;

Session altered.

Elapsed: 00:00:00.00
HUSQVIK@panel_management> INSERT /*+ append */ INTO CITIES
  2  WITH STATES AS (
  3     SELECT 'NY' STATE, 1 ID_OFFSET FROM DUAL UNION ALL
  4     SELECT 'CA' STATE, 2 ID_OFFSET FROM DUAL UNION ALL
  5     SELECT 'TX' STATE, 3 ID_OFFSET FROM DUAL UNION ALL
  6     SELECT 'WA' STATE, 4 ID_OFFSET FROM DUAL UNION ALL
  7     SELECT 'NC' STATE, 5 ID_OFFSET FROM DUAL UNION ALL
  8     SELECT 'SC' STATE, 6 ID_OFFSET FROM DUAL
  9  ),
 10  GENERATOR AS (
 11     SELECT /*+ materialize cardinality(5000000) */ (LEVEL - 1) * 6 ID FROM DUAL CONNECT BY LEVEL <= 5000000
 12  )
 13  SELECT ID + ID_OFFSET, 'City' || (ID + ID_OFFSET) || SYSDATE, STATE, ID + 500 || SYSDATE
 14  FROM GENERATOR CROSS JOIN STATES;

30000000 rows created.

Elapsed: 00:00:13.08
HUSQVIK@panel_management>

30 million rows generated and inserted in 13 seconds. But this is without any indexes. Each index will slow down the insert significantly even though /*+ APPEND */ optimizes their maintenance as well.