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!
How does a sequence work internally ?
Is it stored somewhere or a query is fired and created everytime?
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.
sequence automaticallyt generates unique numbers .It is a sharable object .It is stored and generated independently of tables
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.
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!)
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 advance
how can i create a sequence which will increment in a random fashion?
create sequence semSeq
start with 156
increment by dbms_random.value(1,1000) num
nomaxvalue;
what happens to the incrementor if i just run this query
select sequnce.next_val from dual;
As i faced one production issue in our environment, basically we created sequence number to generate the no in sequence but unfortunately we faced dupicate of sequence number,that means the sequence number has created duplicate .so pls share the view why the same sequence number got generated .
For ex
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
The downside to mysql, of course, is that it’s a steaming pile of crap.