Oracle’s Auto Incrementing with Sequences
My previous post, titled: ‘Sequence-less/Trigger-less Oracle Auto Increment‘ was shot out of the water by my friend and DBA, Jon Emmons. Glad to see that I can be kept in line.
So I have resigned to use Oracle Sequences as a safer means for auto incrementing. But that doesn’t mean that I like it. Here’s what needs to be done to implement auto_incrementing with Oracle:
First, create a sequence:
CREATE SEQUENCE sweet_incrementing INCREMENT BY 1 START WITH 1;
Next write your statement:
INSERT INTO table (id,name) VALUES (sweet_incrementing.NextVal,’bork’);
Now at a second glance, this statement looks much cleaner than my first attempt at subverting the system. However, the fact that you need to create a whole new sequence for each table you wish to have an auto incrementer is pretty stupid when compared to MySQL’s plan of attack. In MySQL you can simply mark a column as a auto_increment during table creation and you’re good to go!
Discuss This Article
|
|
8 Responses to “Oracle’s Auto Incrementing with Sequences”
-
pingback:
Posted: Mar 21st, 2006 at 8:01 pmLife After Coffee » How to Create Auto Increment Columns in Oracle 1 -
satish
Posted: Jul 29th, 2006 at 5:00 amReply to this comment.How does a sequence work internally ?
Is it stored somewhere or a query is fired and created everytime?2 -
Matt
Posted: Jul 29th, 2006 at 6:31 amReply to this comment.Sequences are stored in oracle as objects. The important thing to note is that once .nextval (which increments the sequence by one) has been used, if you were to do a rollback, the sequence would not decrement as one would expect.
3 -
gouthami
Posted: Mar 8th, 2007 at 1:22 amReply to this comment.sequence automaticallyt generates unique numbers .It is a sharable object .It is stored and generated independently of tables
4 -
Joc
Posted: Feb 20th, 2008 at 9:54 amReply to this comment.While your comments about MySQL being a much simpler way of doing things is true, there is a lot more flexibility with the Oracle approach. For example, if you wished to have an incrementing value that is shared by multiple tables but guaranteed unique across them all.
5 -
oldDog
Posted: Apr 20th, 2008 at 7:00 pmReply to this comment.seqName.nextval and seqName.currval and their behavior have several advantages over AUTONUMBER or IDENTITY methods. For example, knowing the PK value to be used on INSERT before performing it saves on the cost of having to ask after the fact.
If you measure 1m executions of last_insert_id() after INSERT to a table, compared to use of seqName.nextval and seqName.currval and their costs, you may find a performance advantage for SEQUENCE. As for the practical matter of having to ask “what value did we get”, seqName.currval is there if you need it (until your next NEXTVAL!)
6 -
Rajesh
Posted: Sep 6th, 2008 at 9:07 amReply to this comment.Its good,
i have a small doubt,
how to find the name of column where sequence is created?Plz, forward to to my mail id rajesh227@gmail.com
tx, in advance7 -
pingback:
Posted: Oct 21st, 2008 at 6:27 amOracle 10g: Using The Returning Clause With ADOdb : BorkWeb Reply to this comment.[...] when you are inserting into a table with auto incrementing values (via a sequence) you have always been able to grab the last value with a simple SELECT statement (line 22): PLAIN [...]
8
[...] Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands. [...]