title: how does oracle makes 1 million insertion in 1 second
body: <p>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</p>
  
  <pre><code>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 &lt; 1000000
  </code></pre>
  
  <p>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.</p>
  
  <p>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</p>
  
  <pre><code>declare i int default 0;
  start transaction;
  when i &lt; 1000000
  do
      insert into tbl values(i, rand(), now() + interval 1 second);
      set i = i + 1;
  end when;
  commit;
  </code></pre>
  
  <p>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</p>
  
  <p>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...</p>