sqloracle-databasebulkinsert

Insert 1 milion record oracle sql where timestamp change

I need something like this

BEGIN
FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO "PAYMENT" (ID, DATETIME) 
VALUES (v_LoopCounter, TO_TIMESTAMP_TZ('2023-05-10 16:32:33.353820500 EUROPE/BERLIN', 'YYYY-MM-DD HH24:MI:SS.FF TZR') + NUMTODSINTERVAL(v_LoopCounter, 'DAY'));
END LOOP;
COMMIT;
END;

That will actually finish in like 5-10 minutes... I need to test the difference in cost with partitioning and without it with an interval of 1 day (NUMTODSINTERVAL(1, 'day')) but I don't really know how to insert 1 milion record (where the insert date actually change each insert) in a small amount of time.

Solution

  • Don't use loops, instead do it in a single statement as it will write (much) less to the log files:

    INSERT INTO "PAYMENT" (ID,DATETIME) 
SELECT LEVEL,TIMESTAMP '2023-05-10 16:32:33.353820500 EUROPE/BERLIN' + NUMTODSINTERVAL(LEVEL, 'DAY')
FROM   DUAL
CONNECT BY LEVEL <= 1e6;