Sequences – short cache/nocache demo

Just a short post about sequences. A lot of people don’t pay much attention to sequences, but they can cause big troubles.

Here is an easy and short example:

Session 1

<br />
SQL&gt; create table tab_seqcache(col1 number)<br />
  2  /</p>
<p>Table created.</p>
<p>SQL&gt; create sequence seq_cache<br />
  2  start with 1 increment by 1 cache 100<br />
  3  /</p>
<p>Sequence created.</p>
<p>SQL&gt; insert into tab_seqcache select seq_cache.nextval<br />
  2  from dual connect by rownum &lt;= 10e5<br />
  3  /</p>
<p>1000000 rows created.</p>
<p>Elapsed: 00:00:02.65</p>
<p>

Session 2

<br />
SQL&gt; create table tab_seqnocache(col1 number)<br />
  2  /</p>
<p>Table created.</p>
<p>SQL&gt; create sequence seq_nocache<br />
  2  start with 1 increment by 1 nocache<br />
  3  /</p>
<p>Sequence created.</p>
<p>SQL&gt; insert into tab_seqnocache select seq_nocache.nextval<br />
  2  from dual connect by rownum &lt;= 10e5<br />
  3  /</p>
<p>1000000 rows created.</p>
<p>Elapsed: 00:01:39.17</p>
<p>

 

Next time be careful when you define a sequence, don’t underestimate its power. 😉

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.