CODEWITHSUNDEEP
mysqlsqldatabaseoracle-databasepostgresql
http8086
http8086

Reputation: 1706

how does oracle makes 1 million insertion in 1 second

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, but on Linux

I was wondering what mechanism oracle have, I'm sure the whole point is the IO with the disk, I know oracle has one Segment for this table(since there is not index), inside that has many extends, and then blocks, I think it allocates more than needs to reduce allocate frequency, bulk flush, better internal structure, but I'm not sure, I don't think only such way can makes such huge improvement, besides I think other rdbms also has such way, but why...

Upvotes: 3

Views: 1946

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

In my desktop Oracle takes 31 seconds and Postgresql 1 second

Oracle:

set timing on
create table tbl as 
select ROWNUM as c1, 
       dbms_random.string('x', 6) as c2, 
       sysdate + ROWNUM / 24 / 3600 as c3
from dual 
connect by level < 1000000;
orcl >   2    3    4    5    6  
Table created.

Elapsed: 00:00:31.33

Postgresql (using _a_horse_with_no_name code):

create table tbl as 
select i as c1, 
    random() as c2, 
    clock_timestamp() + interval '1' second as c3 
from generate_series(1,1000000) i
;
SELECT 1000000
Time: 1168.918 ms

But I do not consider the above numbers meaningful because performance comparisons involve lots of factors which are very difficult to reproduce.

Upvotes: 4

user330315
user330315

Reputation:

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
;

Upvotes: 5

Related Questions