Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    14

    Unanswered: ID with auto_increment is getting too big

    Hello all,

    In all my tables i've made the ID with an auto_increment:

    Code:
    CREATE SEQUENCE "S_TAGS_ID"
    START WITH 1
    INCREMENT BY 1
    CACHE 10;
    
    CREATE OR REPLACE TRIGGER "SYSTEM"."TR_TAGS_ID" 
    BEFORE INSERT 
    ON TAGS
    REFERENCING NEW AS NEW OLD AS OLD 
    FOR EACH ROW 
    BEGIN 
      IF(:new.ID IS NULL) then 
      SELECT S_TAGS_ID.NEXTVAL 
      INTO :new.ID 
      FROM dual; 
      end IF;
    END;
    I've made a script with much (50000) inserts, but 80% of the inserts in this table offend a unique key constraint. so finally i got only 500 "real" inserts in the table.

    Now the problem is that the before_insert_trigger always calls the nextval of the sequencer, so the value of the sequencer does increase with 50000, though i really only insert 500 tags.

    How can i fix it that when a insert is cancelled, the sequencer doesn't increase (without using a merge into when not exists instead of a usual insert)

    Thanks in advance,
    Chielus

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Chielus View Post
    How can i fix it that when a insert is cancelled, the sequencer doesn't increase
    You can't
    Once you retrieve a value from a sequence (i.e. called nextval), that value is gone.

    Why is it a problem that the sequence number increases?

    Is the ID column an artificial primary key? If yes, then it does not matter if the inserted value is 5, 500 or 50000.

    If you need a generator for consecutive numbers without gaps, a sequence is not what you are looking after.

    Btw: you should not create any objects in the SYSTEM schema!

  3. #3
    Join Date
    Mar 2010
    Posts
    14
    Hello shammat.

    The reason why I'm using the system schema is because I use Oracle XE, a stripped version from 10g2 with only one database: system. (or am I wrong?)

    Currently the gaps aren't a big problem, but if I continue using the script like this, the id may get too big and i could have a overflow.

    If you need a generator for consecutive numbers without gaps, a sequence is not what you are looking after.
    what do i have to search for then? For example if I need a generator that "fills" the gaps (generate the first id-number that is free), what can I use for that?

    thanks for the advice, it's quite educative

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Chielus View Post
    Currently the gaps aren't a big problem, but if I continue using the script like this, the id may get too big and i could have a overflow.
    I wouldn't worry about that.

    As you have not specified a maxvalue, the maximun number the sequence will return is 10^27

    Even if you are constantly inserting a thousand records per second you can go on for about 31688764615412794 years without hitting the maximum value of the sequence....

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    and all you have to do is make a new user in oracle and use it. Using system and cause problems in your database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •