You did not mention about performance!
A reader, November 22, 2002 - 4:56 am UTC
Tom,
With your approach, mysequence.nextval in the Insert statement, will the performance will be better than other approaches?
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
Meyer, November 22, 2002 - 6:20 am UTC
This is helpful
STAT...redo size
Zoran Martic, November 22, 2002 - 11:30 am UTC
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?
Todd, November 23, 2002 - 12:32 am UTC
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.
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
J. Sigh, November 23, 2002 - 1:03 am UTC
<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>
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?
Todd, November 23, 2002 - 10:23 am UTC
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.
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?
Todd, November 23, 2002 - 10:41 am UTC
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.
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?
Vinnie, August 07, 2003 - 4:42 pm UTC
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?
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
Tanzy, April 14, 2005 - 12:41 am UTC
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!
April 14, 2005 - 7:37 am UTC
no, you would use a trigger for that, to be "invisible"
Performance of Sequence ID
DrJ, May 12, 2005 - 6:12 pm UTC
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?
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
DrJ, May 16, 2005 - 5:38 pm UTC
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?
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
John Gilmore, May 31, 2005 - 12:31 pm UTC
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.
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
Faisal, June 01, 2005 - 10:13 pm UTC
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
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
A reader, June 02, 2005 - 9:01 pm UTC
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
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?
Nathan Wray, June 06, 2005 - 2:05 pm UTC
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?
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
Nathan Wray, June 06, 2005 - 3:18 pm UTC
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.
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
Eric, June 06, 2005 - 4:50 pm UTC
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.
June 06, 2005 - 5:53 pm UTC
no reason, just the way it is. I would have used select into personlly..
Helena Marková, June 07, 2005 - 6:31 am UTC
Many Sequences?
Mike, June 07, 2005 - 7:00 am UTC
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$?)
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
Vikas Khanna, July 26, 2005 - 3:54 am UTC
I thought to benchmark the comparisons of Oracles 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 Oracles 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 Oracles sequences
Comparison Stats:
Oracles 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)
Lets 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 PIOs do happen as its 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.
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
T. Srinivasan, July 27, 2005 - 7:18 am UTC
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
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.
T. Srinivasan, August 04, 2005 - 11:08 am UTC
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
Abhai Anand, August 29, 2005 - 9:30 am UTC
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.
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
Ajums T T, February 22, 2006 - 9:30 am UTC
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?
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
Mark Wooldridge, February 22, 2006 - 1:18 pm UTC
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
Dheeraj, August 30, 2007 - 10:51 am UTC
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
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
Dheeraj, September 05, 2007 - 12:19 am UTC
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
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
Dheeraj, September 06, 2007 - 9:56 am UTC
As always, Many thanks, Tom!!!
Where is the Stats table in 18 C
Kosmos, June 27, 2019 - 3:41 pm UTC
Hi Tom,
This is a very old post, I am currently using 18C where is the Stats Tables now ?
Thanks,
K
June 28, 2019 - 1:09 pm UTC
How is this related to the original question?