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!


Comments

14 responses to “Oracle’s Auto Incrementing with Sequences”

  1. […] 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. […]

  2. satish Avatar
    satish

    How does a sequence work internally ?
    Is it stored somewhere or a query is fired and created everytime?

  3. 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.

  4. gouthami Avatar
    gouthami

    sequence automaticallyt generates unique numbers .It is a sharable object .It is stored and generated independently of tables

  5. 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.

  6. 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!)

  7. 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 [email protected]
    tx, in advance

  8. […] 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 […]

  9. 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;

  10. Sarma K.M Avatar
    Sarma K.M

    what happens to the incrementor if i just run this query

    select sequnce.next_val from dual;

  11. 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;

  12. The downside to mysql, of course, is that it’s a steaming pile of crap.

  13. Matheus Avatar
    Matheus

    Oracle sequences are indeed superior to MSSQL Server IDENTITY method. But IMHO Oracle lacks a way to bind a sequence to a row, just as SERIAL data type wraps it all on PostgreSQL. When you CREATE TABLE SOME_TABLE (ID SERIAL), PostgreSQL internally gives you a sequence and beautifully sets its NEXTVAL to the ID column.

  14. Bastich Avatar
    Bastich

    Is there a simple example on how to use SEQUENCES in Oracle? I’m a complete newbie – and would like the syntax basics of how to query SEQUENCES.

    Using “select MAXVALUE from some_sequence_table” is throwing
    ‘ORA-02201: sequence not allowed here’ error.