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 Session 2
SQL> create table tab_seqcache(col1 number)
  2  /

Table created.

SQL> create sequence seq_cache 
  2  start with 1 increment by 1 cache 100
  3  /

Sequence created.

SQL> insert into tab_seqcache select seq_cache.nextval 
  2  from dual connect by rownum <= 10e5
  3  /

1000000 rows created.

Elapsed: 00:00:02.65

SQL> create table tab_seqnocache(col1 number)
  2  /

Table created.

SQL> create sequence seq_nocache 
  2  start with 1 increment by 1 nocache
  3  /

Sequence created.

SQL> insert into tab_seqnocache select seq_nocache.nextval 
  2  from dual connect by rownum <= 10e5
  3  /

1000000 rows created.

Elapsed: 00:01:39.17

 

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