Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Meyer.

Asked: November 21, 2002 - 3:41 pm UTC

Answered by: Tom Kyte - Last updated: June 28, 2019 - 1:09 pm UTC

Category: Database - Version: 8.17

Viewed 10K+ times! This question is

You Asked

Tom,

Like most, I put an "ID" column in many tables...

1)What do you consider the best way to populate from sequence:

Quasi SQL from: on insert for each row trigger...

a)select mysequence.nextval
into :new.id
from dual;

b)select nvl(:new.id, mysequence.nextval) into
into :new.id
from dual;

c)OR some other approach... maybe a default like (sysdate for dates)?

The reason I like b over a, is if I ever need to export/truncate/import or for some unknown reason in the future or move records then insert bulk for admin...I will keep any existing values for ID (If forget to disable the trigger). The drawback is someone could insert their own non-sequence value that later gets "steped on" by the sequence.

2)if you were designing a new app, regarding populating ID columns... Would you create one sequence for all tables or one sequence per table? I normally do 1 per table, but now am thinking what difference does it make, they are just arbitrary unique numbers... and the object that hands them out does not serialize.

Thanks,
Meyer





and we said...

I would not use a trigger -- period.

I would use mysequence.nextval in the INSERT statement itself. More performant, 100% clear as to what is happening and export/import won't "hurt" you.


I would create a sequence/table for scalability issues. There are concurrency related issues with regards to sequences. I would want more then one.

and you rated our response

  (29 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

You did not mention about performance!

November 22, 2002 - 4:56 am UTC

Reviewer: A reader

Tom,
With your approach, mysequence.nextval in the Insert statement, will the performance will be better than other approaches?

Tom Kyte

Followup  

November 22, 2002 - 7:55 am UTC

<b>In a huge way -- it is faster and less resource intensive.  Here is a simple test showing it is measurably faster and then we see it uses tons less resources (look at the redo size -- triggers can cause additional redo generation)</b>


ops$tkyte@ORA920.US.ORACLE.COM> create table t1 ( x int primary key, y int );
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table t2 ( x int primary key, y int );
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create sequence s;
Sequence created.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace trigger t2_trigger before insert on t2 for each row
  2  begin
  3      select s.nextval into :new.x from dual;
  4  end;
  5  /
Trigger created.

ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      type rc is ref cursor;
  7      l_cur rc;
  8  begin
  9      insert into run_stats select 'before', stats.* from stats;
 10
 11      execute immediate 'alter session set session_cached_cursors=0';
 12      l_start := dbms_utility.get_time;
 13      insert into t1 (x,y) select s.nextval, 1 from all_objects;
 14      for i in 1 .. 10000
 15      loop
 16          insert into t1(x,y) values ( s.nextval, 1 );
 17      end loop;
 18      commit;
 19      l_run1 := (dbms_utility.get_time-l_start);
 20      dbms_output.put_line( l_run1 || ' hsecs' );
 21
 22      insert into run_stats select 'after 1', stats.* from stats;
 23      execute immediate 'alter session set session_cached_cursors=100';
 24      l_start := dbms_utility.get_time;
 25      insert into t2 (y) select 1 from all_objects;
 26      for i in 1 .. 10000
 27      loop
 28          insert into t2(y) values ( 1 );
 29      end loop;
 30      commit;
 31      l_run2 := (dbms_utility.get_time-l_start);
 32      dbms_output.put_line( l_run2 || ' hsecs' );
 33      dbms_output.put_line
 34      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 35
 36      insert into run_stats select 'after 2', stats.* from stats;
 37  end;
 38  /<b>
1123 hsecs
4206 hsecs
run 1 ran in 26.7% of the time
</b>
PL/SQL procedure successfully completed.


ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
...
LATCH.enqueue hash chains           12238      12882        644
LATCH.cache buffers lru chain        1035       2158       1123
STAT...session cursor cache hi          0       1391       1391
ts

LATCH.checkpoint queue latch          594       2062       1468
STAT...recursive cpu usage            901       3770       2869
LATCH.simulator hash latch           4714       8862       4148
STAT...table scan blocks gotte          4      39303      39299
n

STAT...table scan rows gotten          70      39369      39299
STAT...table scans (short tabl          1      39300      39299
es)

STAT...execute count                12015      51321      39306
STAT...no work - consistent re      51167      90481      39314
ad gets

STAT...buffer is not pinned co      66931     106255      39324
unt

STAT...recursive calls              37952      77314      39362
LATCH.session allocation             1034      56536      55502
STAT...redo entries                 25649      83899      58250
LATCH.redo allocation               25728      84383      58655
STAT...db block gets                39473     127768      88295
LATCH.library cache pin alloca       8157     120162     112005
tion

LATCH.shared pool                   59586     175234     115648
STAT...db block changes             50986     167978     116992
STAT...calls to get snapshot s      16321     134191     117870
cn: kcmgss

STAT...consistent gets             140316     258205     117889
STAT...session logical reads       179789     385973     206184<b>
LATCH.library cache pin            114812     328285     213473
STAT...session pga memory max      260724     524288     263564
LATCH.library cache                161012     615249     454237
LATCH.cache buffers chains         442879    1062600     619721
STAT...redo size                  8577744   20685844   12108100</b>

97 rows selected.

ops$tkyte@ORA920.US.ORACLE.COM>


 

Thank You

November 22, 2002 - 6:20 am UTC

Reviewer: Meyer from Baltimore, MD

This is helpful

STAT...redo size

November 22, 2002 - 11:30 am UTC

Reviewer: Zoran Martic from Ireland

Hi Tom,

I produced the similar results in my previous month tests.
The only thing I did not reproduce was:
STAT...redo size

When I run your example I realized what is making redo size that big.

insert into t2 (y) select 1 from all_objects;

I am wondering why (my brain is not working properly on Friday)?
It looks to me a little bit strange.

If you exclude this statement and use only pure inserts (I put 100000 instead of 10000 for your loop) as you will probably use in most applications you will not have the big difference in redo size.

Thanks in advance,
Zoran

How does this affect bind variables?

November 23, 2002 - 12:32 am UTC

Reviewer: Todd from Minneapolis, MN USA

Tom,
You wrote:
I would use mysequence.nextval in the INSERT statement itself.

Is mysequence.nextval considered a literal and hence would need to be parsed for each insert from an application program (not plsql)? Our programs use mysequence.nextval from dual in a separate statment to place it in a variable. Are we doing extra work for no reason?

Thanks.


Tom Kyte

Followup  

November 23, 2002 - 9:32 am UTC

you are doing extra work and lots of it for no good reason.  It is not a literal (they are things that could appear in quotes).  It is a reference to a database object.

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create sequence s;

Sequence created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> declare
  2      l_start number;
  3      l_run1  number;
  4      l_run2  number;
  5
  6      l_seq   number;
  7  begin
  8      insert into run_stats select 'before', stats.* from stats;
  9
 10      l_start := dbms_utility.get_time;
 11      for i in 1 .. 30000
 12      loop
 13          insert into t values ( s.nextval );
 14      end loop;
 15      l_run1 := (dbms_utility.get_time-l_start);
 16      dbms_output.put_line( l_run1 || ' hsecs' );
 17
 18      insert into run_stats select 'after 1', stats.* from stats;
 19      l_start := dbms_utility.get_time;
 20      for i in 1 .. 30000
 21      loop
 22          select s.nextval into l_seq from dual;
 23          insert into t values ( l_seq );
 24      end loop;
 25      l_run2 := (dbms_utility.get_time-l_start);
 26      dbms_output.put_line( l_run2 || ' hsecs' );
 27      dbms_output.put_line
 28      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' );
 29
 30      insert into run_stats select 'after 2', stats.* from stats;
 31  end;
 32  /
925 hsecs
2068 hsecs
run 1 ran in 44.73% of the time

PL/SQL procedure successfully completed.


ops$tkyte@ORA920.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
  2         ( (c.value-b.value)-(b.value-a.value)) diff
  3    from run_stats a, run_stats b, run_stats c
  4   where a.name = b.name
  5     and b.name = c.name
  6     and a.runid = 'before'
  7     and b.runid = 'after 1'
  8     and c.runid = 'after 2'
  9     and (c.value-a.value) > 0
 10     and (c.value-b.value) <> (b.value-a.value)
 11   order by abs( (c.value-b.value)-(b.value-a.value))
 12  /

NAME                                 RUN1       RUN2       DIFF
------------------------------ ---------- ---------- ----------
...
STAT...recursive calls              51177      81188      30011
LATCH.shared pool                   70751     101007      30256
LATCH.library cache pin            135274     195481      60207
LATCH.library cache                174525     234943      60418
STAT...calls to get snapshot s      34566     124568      90002
cn: kcmgss

STAT...consistent gets               1646      91654      90008
STAT...session logical reads        37322     127338      90016
LATCH.cache buffers chains         173012     354904     181892

80 rows selected.


Look at the wall clock time but really look at the additional latching.  (look also at all of the keystrokes you used ;) 

Wowee, look at the time

November 23, 2002 - 1:03 am UTC

Reviewer: J. Sigh from MA, USA

<rant>
Here is another one of those "KNOWN" things shot down.
I've read "Put it as close to "IN" the database whenever you can" ish.
How much closer than a trigger can you get to the tables?
I would have expected the opposite.
Now what?
I've insisted on trigger sequence generation. The boss opts for the
"SELECT some_sq.NEXTVAL INTO n_prim_key FROM DUAL;" then the n_prim_key in the insert. (I've always thought this was not good, network hit-wise(double-hit)(debunk me, man :) ))
Anyway, from the fat client on a form60 front end, the boss gets a value back and I get a slower, less scalable, sequence. Ugh!
the more I read you (and it's been since the Web version started) the dumber I feel.
sigh!!!!!
</Rant>


Tom Kyte

Followup  

November 23, 2002 - 9:39 am UTC

well, don't be so harsh - truth be told you were both doing it the non-optimal way.

But, this does point out something. If you use this test harness:
</code> http://asktom.oracle.com/~tkyte/runstats.html <code>
or something similar (tkprof), you'll be able to conclusively prove the answer.


Also -- there are other things to consider

o your trigger covers everyone. it is "complete"

o some people don't like putting the sequence reference in the insert, if "offends" them for some reason (generally MS-SS programmers fall into this category) so the trigger is nice for them.



JDBC support for sequence reference in insert?

November 23, 2002 - 10:23 am UTC

Reviewer: Todd from Minneapolis, MN USA

Tom,
Does jdbc (thick or thin) support the use of sequence reference in an insert statement? (It is on my list to learn more about the jdbc drivers.)

Thanks.


Tom Kyte

Followup  

November 23, 2002 - 10:31 am UTC

yes. both. the jdbc drivers don't really "see" the sql, it is just a string to them for the most part.

What was the value of the sequene?

November 23, 2002 - 10:41 am UTC

Reviewer: Todd from Minneapolis, MN USA

Tom,
Hopefully just one more thing on sequences. I appreciate your responses.
A developer asked about having the value of the sequence (key) for use in inserting child rows. If the nextval is imbedded in the insert, how do they determine the value used by the insert without reading the row back ?
Thanks.

Tom Kyte

Followup  

November 23, 2002 - 11:36 am UTC

CURRVAL ;)

insert into parent ( id, ... ) values ( my_parent_seq.nextval );
insert into child ( id, fkey, ... ) values ( my_child_seq.nextval,
my_parent_seq.CURRVAL,
.....


Here is a good article I just read on sequences that explains them (note from Nov2002 -- in the future, the link could be dead)
</code> http://www.dbazine.com/gulutzan4.html <code>


or, alternatively, you can use the insert returning syntax:

begin

insert into parent( id, ... ) values ( my_parent_seq.nextval, ... )
returning ID into l_id;

dbms_output.put_line( 'the new row is ' || l_id );

end;


What about Blobs?

August 07, 2003 - 4:42 pm UTC

Reviewer: Vinnie from Orlando

We are inserting a blob into are table via a JAVA program with RECID as the PK which is populated via a sequence. We We are using BIND variables & want to do the entire insert in a batch. But the problem is we need the RECID to be able to go back & insert the BLOB. Is there anyway to get a block of numbers from the sequence? What is your approach?

Tom Kyte

Followup  

August 10, 2003 - 11:11 am UTC

why not:


begin
insert into t (c1, c2) values ( recid.nextval, empty_blob() )
returning c1, c2 into ?, ?;
end;


and preparing that callable statement, binding the int output and the blob output. In that fashion, you get both the new sequence and the blob locator in one trip -- it is already locked (assuming autocommit is off which it MUST be for all java programs that want to work correctly)

Sequence as default Value

April 14, 2005 - 12:41 am UTC

Reviewer: Tanzy from India

Can we use sequence values as default values in the create table syntax, for example

create table mytable(id number default my_seq.nextval, name varchar2(20));

Something like this....

So when i insert rows in the table, i need not mention the id value, it should be automatically picked.

Kindly suggest!

Tom Kyte

Followup  

April 14, 2005 - 7:37 am UTC

no, you would use a trigger for that, to be "invisible"

Performance of Sequence ID

May 12, 2005 - 6:12 pm UTC

Reviewer: DrJ from Port Washington, NY

It may surprise you, given this discussion, that our Oracle DBA is staunchly opposed to using Oracle sequence ID's REGARDLESS of the specifics of the implementation. Scalability is the sine qua non of our application, since it takes more than 10,000 complex orders/hour at peak. He argues that the application (J2EE) should generate a GUID as the ID. This means inserting (and potentially indexing) a 16 or 32 byte alphanumeric (CHAR or VARCHAR2) in as many as 10 different tables for each order. I would think the Oracle sequence ID would be far more efficient/performant.

What's your view?

Tom Kyte

Followup  

May 13, 2005 - 9:00 am UTC

why are they staunchly opposed to it?

what is the technical reason? the only way to fight FUD (fear uncertaintity doubt) is to expose the underlying cause of the fear.


It'll be a 16 byte RAW or 32 byte varchar2.


The sequence seems to me to be the only way to go really, I cannot fathom a reason for not doing so. I'd need help understanding why (especially coming from a DBA?)

Objections to sequence ID

May 16, 2005 - 5:38 pm UTC

Reviewer: DrJ from Port Washington, NY USA

The dba's argument is that all sequence ID's utilize the same latch and therefore cannot be easily/inexpensively horizontally scaled (as opposed to a software GUID that can be horizontally scaled by adding webservers. He also presents suggests that the semaphore mechanism underlying the latch has a sizable CPU hit which can be problematic if the CPU gets near to being pinned -- this does happen at peak times). He sees the potential performance issues of using a 32-byte VARCHAR2 + indexes is insignificant in comparison with the latch/semaphor concern.

What do you think?

Tom Kyte

Followup  

May 16, 2005 - 6:14 pm UTC

I think they are overstating any issues...

If there is even an issue.

for the largest and biggest and busiest databases in Oracle use... well sequences.

(gosh, so many other important things to think about aren't there)

but whatever. I've not really see the sequence cache latch be a significant issue, especially with a mere 10,000 things per hour (i just generated 100,000 of them in 10 processes each (a million of them) in seconds).

up to you guys, sometimes it just ain't worth arguing about.

Just make sure you use bind variables
and you parse AS LITTLE AS HUMANLY POSSIBLE

for those will be the big issues, this bit of noise won't really factor in.

If your java coders don't bind
If your java coders insist on "prepare, bind, execute, close, goto prepare"

that will be a real measurable problem.



Sequence Overhead Issues

May 31, 2005 - 12:31 pm UTC

Reviewer: John Gilmore from Liverpool, United Kingdom

Hi Tom,

In your answer to the initial question you stated that "I would create a sequence/table for scalability issues. There are concurrency related issues with regards to sequences".

However in your answer to the previous post you don't seem to consider "... the sequence cache latch (to) be a significant issue".

On my current site they use one sequence to populate the surrogate primary keys of about twenty tables. I'm just wondering how big a deal this actually is.

Tom Kyte

Followup  

June 01, 2005 - 7:44 am UTC

My point above was -- many (all pretty much) big Oracle systems use sequences. For a DBA to be focusing in on that is somewhat "not the place I would start" (nor in fact even ever get to -- when was the last time or first time you saw this as the biggest issue on your systems?)

However, I would consider using more than one sequence (sys for example uses many dozens of them). The issue would be more with many people going after the same sequence number.


Are you highly concurrent? 10's, 100's of transactions per second? could be an issue (you'd see the waits). Slow system? Wouldn't be an issue.

Sequence in Insert Statement

June 01, 2005 - 10:13 pm UTC

Reviewer: Faisal from Canada

Hi Tom,

I have two tables stg1 and stg2. stg1 is parent of stg2. I am loading the data using external table. How could I generate the pk and fk during the bult insert? I want some thing as follows;

insert into stg1 ( id, ... ) select stg1_seq.nextval, ... from external table;

insert into stg2 ( id, fkey, ... ) select stg2_seq.nextval, ?,... from external table;

Please help !

Faisal


Tom Kyte

Followup  

June 02, 2005 - 4:23 pm UTC

need more information -- how would you know what key to assign in *any* case. I mean, well, how do you know what parent record a child goes with in the first place?

More info here

June 02, 2005 - 9:01 pm UTC

Reviewer: A reader

Hi Tom,

The business key of stg1 table is clm_no+process_date, stg2 which is a transaction table has the above key as a foriegn key. I inforced the business key as unique index, on stg1 table. There is no primary key of stg2 table. Now for both stg1 and stg2 tables, I want to create surrogate keys and to establish a relation between two tables.

Hope it will help....

Thanks




Tom Kyte

Followup  

June 03, 2005 - 7:06 am UTC

insert into stg1 ( id, ... ) select stg1_seq.nextval, ... from external table;

insert into stg2 ( id, fkey, ... )
select stg2_seq.nextval, stg1.surrogate_key, ...
from external, stg1
where external.business_key = stg1.business_key;


Re: What about Blobs?

June 06, 2005 - 2:05 pm UTC

Reviewer: Nathan Wray from Detroit Rock City

Hi Tom, thanks for all the great Oracle tips.

I was interested in Vinnie's question about sequences and batch updates, but your reply was a bit misleading since you suggested binding OUT variables. While your reply was correct for a single insert, it wouldn't be useful for batch inserts since you can't use OUT parameters in batch insert/update.

I am trying to load 250000 records in a batch insert, into a table with a sequence based primary key. Each of the new records is backed by Java object, and after the insert I would like each object's primary key member to be set to the correct new value. I break the insert into chuncks of 1000. Currently I'm first calling a statement to get a new primary key 1000 times, setting the new value into each object, then doing a batch insert with the new key value that I had selected. I feel like there must be a way to get a mass of sequence values to use in a batch insert but I haven't found it. I found one reference to jumping the sequence forward by changing the all_seqences table but I'd like to avoid it if possible.

I don't believe curval is useful in this context, any suggestion?



Tom Kyte

Followup  

June 06, 2005 - 3:03 pm UTC

why was it misleading? you bind outputs, you bind inputs.

with lots_of_rows
as
(
select level l
from dual
connect by level <= 100
)
select s.nextval from lots_of_rows;


will get you 100 sequences. (you really don't want to save up 250,000 records and then send, you want back and forth and back and forth, keep a stream going. Give lgwr a change to flush the redo log buffer while you are generating/binding the next 100 rows, give dbwr a chance to keep the buffer cache clean enough so you have free buffer cache blocks when you need them -- you do some work, db does some work -- back and forth)

make sure to alter the sequence so the cache size is a more reasonable value, say 100 or 1000 -- to avoid the recursive sql that would otherwise happen.

Re: Batch and Callable Statement

June 06, 2005 - 3:18 pm UTC

Reviewer: Nathan Wray from Detroit


Thanks for the awesome select Tom. I am never disappointed with this column.

Back to the misleading part, if it was not misleading then at least I confused myself on reading it. I am currently doing the batch inserts using a prepared statement in Java using addBatch many times, then finally executeBatch(). I tried converting this to use a CallableStatement with a registered OUT parameter, then realized that there's no mechanism to get the Nth OUT parameter after executing the batch insert. And actually OUT parameters aren't allowed with batch inserts. So the two concepts (OUT parameter and batch insert) don't mesh in Java.



Tom Kyte

Followup  

June 06, 2005 - 3:26 pm UTC

ahh,

begin
insert into t (c1, c2) values ( recid.nextval, empty_blob() )
returning c1, c2 into ?, ?;
end;


it is a plsql block see... so, in/out

HTML DB generated code to get next sequence value

June 06, 2005 - 4:50 pm UTC

Reviewer: Eric from Rochester, NY USA

Hi Tom,

Is there a reason to use a loop to retrieve the next primary key value as in the following code?

declare
function get_pk return varchar2
is
begin
for c1 in (select TIME_SCHEDULE_SEQ.nextval next_val from dual)
loop
return c1.next_val;
end loop;
end;
begin
:P3_TS_ID := get_pk;
end;

The above code was generated by Oracle HTML DB. I assume there is a reason for doing it this way, but cannot figure out what it could be.

I asked the question on the HTML DB discussion forum and didn't get a response from the HTML DB team but one reader thought it might be in case DUAL had more than one row...

Thank you.

Tom Kyte

Followup  

June 06, 2005 - 5:53 pm UTC

no reason, just the way it is. I would have used select into personlly..

June 07, 2005 - 6:31 am UTC

Reviewer: Helena Marková from Bratislava, Slovakia


Many Sequences?

June 07, 2005 - 7:00 am UTC

Reviewer: Mike from England

Hi Tom,

You state previously that you would use a PK per table as you would be worried about
<Quote>
The issue would be more with many people going after the
same sequence number.
</Quote>

I'm curious, as far as I am aware all sequences are underpinned by the same underlying object that will be the subject of any latching serialisation etc.

On the assumption that a sequence has a suitable cache to stop the underlying object getting constantly hit, where does the scalability of multiple sequences come from? they all end up at the same place (is it SEQ$?)

Tom Kyte

Followup  

June 07, 2005 - 8:24 am UTC

it was mostly the caching issue, everyone going after the same sequence value and having to more frequently do the recursive sql to update seq$ and whilst that is happening -- everyone else waits as well.

it is hard to convince people that cache 1000 or even more is perfectly OK, that sequences will have gaps, that a gap of 100,000 is ok to have and no more or less painful than a gap of 1.


Comparison of Oracle Sequence Vs Self generated sequence

July 26, 2005 - 3:54 am UTC

Reviewer: Vikas Khanna from INDIA

I thought to benchmark the comparisons of Oracle’s own sequence Vs self generated sequence (via a table, procedure & function) had Oracle not come up with the concept of sequence. 

The results are dramatically in favor of Oracle’s sequence since the code to generate the sequences has been written in the innermost layer of the KERNEL which is written in C.  

The Results prove that Oracle provides you 

•    More scalibility, 
•    More performance (~493%) and hence 
•    More adaptability 

when using Oracle’s sequences

Comparison Stats:

Oracle’s own Sequence:

Pre-Requisites:

A Sequence
CREATE SEQUENCE "SCOTT"."EMP_SEQ" INCREMENT BY 1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;

/* CREATE SEQUENCE CACHE causes us to cache values for the sequence in memory. Since we have cached 20 values – Oracle updates seq$ every 20 requests.*/

A Table

Create table t_with_seq (x INT); 

/* where the rows will be inserted after retrieving the values from the sequences. This table can be thought of a transactional table where in many sessions will try to insert the rows depending on the application usage. */

A Procedure 

CREATE OR REPLACE PROCEDURE GET_WITH_SEQUENCE
AS
Begin 
Declare
X INT;
l_ret_Value INT;
l_start NUMBER;
l_elapsed Number;
Begin
      l_start := dbms_utility.get_time;
      X := 1;
      While (x<=10000) LOOP         
           Select emp_seq.nextVal INTO l_ret_Value from dual;    
           Insert into t_with_seq Values (l_ret_Value);
           x := x + 1;
      end loop;
      Commit;
      l_elapsed := (dbms_utility.get_time-l_start);
      dbms_output.put_line( l_elapsed || ' hsecs' );
end;

/* This procedure is simulated to retrieve the sequence values and insert into the above mentioned table 10,000 rows. */

Execution Statistics
SQL> SET SERVEROUTPUT ON SIZE 100000;
SQL> exec get_with_sequence;
349 hsecs

PL/SQL procedure successfully completed.

/* The procedure got executed in 349 hsecs, retrieving 10,000 sequence values and simultaneously inserting them into the table */

Vs

Self Generated Sequence: (Had Oracle not implemented the concept of Sequence) 

Pre-Requisites:

A Table (Requires Extra Storage)

SQL> desc Sequences;
 Name                   
 ---------------------
 SEQ_ID                 NUMBER
 SEQ_NAME            VARCHAR(20)   
 START_VALUE        NUMBER    
 END_VALUE            NUMBER  
 CURR_VALUE             NUMBER    
 INCREMENT_BY       NUMBER     
 ASC_DESC             CHAR (1)     

Let’s have a ROW already inserted based on which the new incremental values will be generated.        

1    SEQ_TEST        1    99999999999999        1    1    A

/* to have Values either ‘A’ or ‘D’ to represent Ascending and Descending. */ 

SQL> Alter table Sequences CACHE;        
Table altered.
/* Just to make sure that NO PIO’s do happen as it’s a small Lookup table */


A Function
CREATE OR REPLACE FUNCTION fn_get_sequence_Number (my_seq_id INT)
RETURN Number
AS
    pragma autonomous_transaction;
Begin
      Declare
           l_curr_Val INT;
           l_Incr_By INT;
           l_asc_desc Char;
      Begin
      Select Curr_Value,Increment_By,Asc_Desc into l_Curr_Val, l_Incr_By,l_asc_desc  
      from Sequences where Seq_id = my_seq_id for update;
      /* for update is used to exclusive lock that row for that session till it 
      commits/rollbacks */
           If l_asc_desc = 'A' then
        Set_Curr_Value = l_ curr _Val + l_Incr_By
           Else
        Set_Curr_Value = l_ curr _Val -  l_Incr_By
           End if;
Update Sequences Set Curr_Value = Set_Curr_Value where seq_id = my_seq_id;
Commit;
RETURN Set_Curr_Value;
End;
End;

A Procedure 
CREATE OR REPLACE PROCEDURE GET_WITHOUT_SEQUENCE
AS
Begin 
Declare
X INT;
l_ret_Value INT;
l_start NUMBER;
l_elapsed Number;
Begin
      l_start := dbms_utility.get_time;
      x := 1;
      While (x<=10000) LOOP 
           Select fn_get_sequence_Number(1) INTO l_ret_Value from dual;    /*Retrieving the Value from the funtion based on Sequence_id */
           Insert into t_without_seq Values (l_ret_Value);    
    /* Inserting the value retrieved into the table */
           x := x + 1;
      end loop;
      Commit;
      l_elapsed := (dbms_utility.get_time-l_start);
      dbms_output.put_line( l_elapsed || ' hsecs' );
End;

Execution Statistics
SQL> SET SERVEROUTPUT ON SIZE 100000;
SQL> exec GET_WITHOUT_SEQUENCE;
2070 hsecs

Questions: I do agree that Oracle sequences are the best way to generate the pk values, but while comparison can we somehow cache the values under table implementation like we did for sequence using CACHE option. This will definitely reduce the Select.....for update calls to the Server call by call.

Would be better if you can please illustriate with an example carrying forward.

Thanks in anticipation. 

Tom Kyte

Followup  

July 26, 2005 - 8:11 am UTC

not worth the effort is it. besides, you'd have to "cache" per session, not per instance as the sequence can. so it would not be remotely similar.

but, not at all worth the energy.

Sequences used in Insert Statement

July 27, 2005 - 7:18 am UTC

Reviewer: T. Srinivasan from India

Tom,

We are inserting the sequence.nextval for the id field
for which there is a primary key defined.

There are times when we run the insert statement(thru Procedure) we get unique constraint violation error. When we just rerun the insert statement(Procedure) it goes thru without any problem. We had created the sequence without specifying any option on the cache. We did check the sequence value and the maximum id value available at the table and found that sequence.nextval is always greater than the id column value available at the table. Can you please help in this regard



Tom Kyte

Followup  

July 27, 2005 - 10:14 am UTC

sorry, but something else is happening here. The sequence will NOT return the same value twice.

Sounds more like

a) we created the table
b) we put data into it
c) someone either modified an existing row and updated the primary key (not USING the sequence) or they inserted a row NOT USING the sequence. Say they put in primary key = 1234. The sequence is currently sitting at 1200.

d) you load more data, the sequence decided to return 1234. Primary key violation (not because the sequence returned the same number but because some other process is NOT USING THE SEQUENCE).

e) you retry and the sequence says "well, last time I gave you 1234, let me give you 1235 this time" and it works because 1235 really didn't exist.


I think you have something/someone creating data via a non-standard technique and they are messing you up.

August 04, 2005 - 11:08 am UTC

Reviewer: T. Srinivasan from INDIA

Thanks for your response and it was extremely helpful and we found that the while recreating the sequence , it was created with max(id) from the table instead of max(id+1) , which was the cause for this error. However thanks a lot for your reply

Same sequence value for two session user

August 29, 2005 - 9:30 am UTC

Reviewer: Abhai Anand from India

Hi Tom,
I face an issue that while generation the ID from one of our sequence gets the same sequence value. This same value the user is getting after every 8 sequence gets menad for 9 and 10 session. Here in the properties of sequence the Chache value is 10.

Tom Kyte

Followup  

August 29, 2005 - 1:41 pm UTC

*not possible*


quite simply *not possible*

Sorry, but I believe something else is happening here, unless you have a very small maxvalue and CYCLE on the sequence (and hence it is just wrapping around, but then every value would be duplicated) sequences *just don't work this way*

Something else is happening, you'll have to reproduce this (and in the attempt to reproduce this, you'll find the mistake made somewhere in the code).

sequences do not create duplicate values, I've never seen it - or heard of it - ever.

Adding a sequence column to an existing table

February 22, 2006 - 9:30 am UTC

Reviewer: Ajums T T from Mumbai, IN

Hi Tom,
We have an existing table with more than 50,000 records in it. We need to add an id column to the table. This column will store sequences.

Is there some method of getting the sequence values populated when I alter the table to add a column?

Tom Kyte

Followup  

February 22, 2006 - 10:10 am UTC

You'll need to update this after the fact, if you

a) alter table add column
b) lock table and update it
c) alter table add constraint to make it a primary key

it should only take a couple of seconds.  50,000 records is tiny.


ops$tkyte@ORA9IR2> create table t
  2  as
  3  select * from big_table.big_table
  4   where rownum <= 50000;

Table created.

Elapsed: 00:00:00.35
ops$tkyte@ORA9IR2> create sequence s;

Sequence created.

Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2> alter table t add myid number;

Table altered.

Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2> lock table t in exclusive mode;

Table(s) Locked.

Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> update t set myid = s.nextval;

50000 rows updated.

Elapsed: 00:00:01.83
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(myid);

Table altered.

Elapsed: 00:00:00.28
 

using with rowtypes - a nice to have

February 22, 2006 - 1:18 pm UTC

Reviewer: Mark Wooldridge from Warrenton, VA

I build core table API's for most of my applictions to handle the insert,update and delete functions. The insert is similar to

function i_table_name(p_rec in table_name%rowtype)
return number is
v_rec table_name%rowtype;
begin
v_rec := p_rec;
select table_name_id_s.nextval
into v_rec.table_name_id
from dual;

insert
into table_name
values v_rec;

return v_rec.table_name_id;
end i_tablename;

My question/comment is that it would be nice to combine the select of the sequence and insert into on statement, something like:

function i_table_name(p_rec in table_name%rowtype)
return number is

v_id number;

insert
into table_name
values p_rec
overriding table_name_id with table_name_id_s.nextval
returning table_name_id into v_id;

return v_id;

end i_table_name;

The other solution I have used is to make things procedures and have the parameter and in/out, this way the calling program can get back the id and any other values set by the set (e.g. audit info - created_by, created_date).

The overriding syntax would also be helpfull with updates when a %rowtype is passed in.

Example: a table API to ensure the audit info is correctly set.
update table_name
set row = p_rec
where id = p_rec.table_name_id
overriding updated_by with sys_context(....),
updated_date with sysdate;

The overriding syntax would alleviate the need for a local copy of the %rowtype;


Associating sequences with primary key of a table

August 30, 2007 - 10:51 am UTC

Reviewer: Dheeraj from India

Hi Tom,

I have a requirement where-in I am initially inserting some rows in a table by hard-coding primary key (with digits -> 1,2,3...) and at a later point of time, I want to use a sequence that automatically detects the max value of primary key column and generates max(PK) + 1 as the next column value.
All in all, can I associate a sequence with primary key of a table, such that whenever this sequence is used to populate primary key, it detects the max value of that column and generates max + 1.

Test case:

create table test
(id number constraint test_pk primary key);

insert into test
values(1);

insert into test
values(2);

insert into test
values(3);

select * from test;

ID
----------
1
2
3

Now, my requirement is to use a sequence such that it is associated with test_pk And whenever I use this sequence to populate test table, it detects the max value of the ID column and generates, MAX + 1, 4 in above example.

I don't want to create a sequence that has *start with* clause as MAX(id) of test table.

Hope I am able to explain my query.

Many thanks,

Dheeraj
Tom Kyte

Followup  

September 04, 2007 - 5:25 pm UTC

are you trying to do a gap free sequential number?

if so, you will have to serialize of course.

and I don't like you doing that to our database - you make us look bad.


why do you *need* this, what is the actual true live real world bona-fide business case.

Associating sequences with primary key of a table

September 05, 2007 - 12:19 am UTC

Reviewer: Dheeraj from India

Hi Tom,

I am okay with both gap free and with gaps sequential numbers.

What I am trying to do is to seed the initial data without getting into sequential nos. since there is lot of PK-FK relationship within tables and I want my initial seeding of data as simple as hard-coding the PKs of all the tables. However, once system is up and running with my seeded data, I want to start usage of sequence logic for all the tables.

Cheers,

Dheeraj
Tom Kyte

Followup  

September 05, 2007 - 2:02 pm UTC

that is NOT what you stated. You wrote:

....
All in all, can I associate a sequence with primary key of a table, such that whenever this
sequence is used to populate primary key, it detects the max value of that column and generates max
+ 1.
........


if what you are saying NOW is true, then you can simply query the table(s) after the load and find the max value and use "start with" on the create sequence.


You could write a simple procedure that received the tablename and column_name to select max(column) from and automate this of course.

Associating sequences with primary key of a table

September 06, 2007 - 9:56 am UTC

Reviewer: Dheeraj from India

As always, Many thanks, Tom!!!

Where is the Stats table in 18 C

June 27, 2019 - 3:41 pm UTC

Reviewer: Kosmos from USA

Hi Tom,
This is a very old post, I am currently using 18C where is the Stats Tables now ?

Thanks,
K
Chris Saxon

Followup  

June 28, 2019 - 1:09 pm UTC

How is this related to the original question?