how does oracle makes 1 million insertion in 1 second

Question

Few years ago, I found oracle can achieve 1 million insertion in 1 second, first let me explain this, I remember in SQL/PLUS interactive command line ``` set timing on create table tbl as select ROWNUM c1, dbms_random.string('x', 6) c2, sysdate + ROWNUM / 24 / 3600 c3 from dual connect by level < 1000000 ``` above code will create one table with 1 million records, and SQL/PLUS will give you the elapsed time. That was on Windows XP(SP3) PC machine, vendor is Lenovo Think Centre, forgot the model, hardware performance was not bad. But I tested MySQL PostgreSQL even sqlite, because I could not find one statement to generate millions result for them, then I made one loop, something like ``` declare i int default 0; start transaction; when i < 1000000 do insert into tbl values(i, rand(), now() + interval 1 second); set i = i + 1; end when; commit; ``` not exactly, something like that, but it takes several seconds to finish that, only makes 200~300K per second, basically the same hardware,…

Accepted Answer

You are comparing apples to oranges. In Oracle you are doing a create table based on a select which is a bulk insert. The second code example is doing 1 million single inserts which are always way slower than a bulk insert.

You also tagged this with Postgres, so please try the following there:

```
create table tbl as 
select i as c1, 
       random() as c2, 
       clock_timestamp() + interval '1' second as c3 
  from generate_series(1,1000000) i
;
```

how does oracle makes 1 million insertion in 1 second

Solution

Problem