This discussion is archived
12 Replies Latest reply: Apr 2, 2013 9:16 AM by chris227 RSS

sequance nextval

997232 Newbie
Currently Being Moderated
Hi i got sequance
CREATE SEQUENCE "BAZA"."SEKW_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 10001 CACHE 10000 NOORDER NOCYCLE ;

and trigger

create or replace
trigger doda_id
BEFORE INSERT ON spolki
FOR EACH ROW
BEGIN
:new.spolka_id :=sekw_id.nextval;
END;


when i delete the row i created it gives me new value like i created 1,2 and deleted 1 then run trigger and i have 2,3 how to do it to have 1,2 after running trigger?
  • 1. Re: sequance nextval
    SomeoneElse Guru
    Currently Being Moderated
    how to do it to have 1,2 after running trigger?
    Nope. Triggers don't work that way. They are just generators with no intelligence.

    Edited by: SomeoneElse on Apr 2, 2013 7:53 AM

    (changed the word counters to generators)
  • 2. Re: sequance nextval
    997232 Newbie
    Currently Being Moderated
    so what should i do to fix this ??
  • 3. Re: sequance nextval
    jeneesh Guru
    Currently Being Moderated
    994229 wrote:
    so what should i do to fix this ??
    Dont fix it..

    It should be 2 a,nd 3 only..

    Why do you want to make it 1 and 2 ? Any business rule? What about the records in child tables, when you change the ID? What you want to happen in a multi user environment?

    And it will lead to serialization..

    See all these discussions:

    sequence numbers - with no gaps!
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1133611000346070688
  • 4. Re: sequance nextval
    SomeoneElse Guru
    Currently Being Moderated
    so what should i do to fix this ??
    Why do you need this behavior?

    What business purpose does it solve?
  • 5. Re: sequance nextval
    997232 Newbie
    Currently Being Moderated
    i want to make continous numbering after deleting row
  • 6. Re: sequance nextval
    sybrand_b Guru
    Currently Being Moderated
    Why? Do these numbers have meaning to you?

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: sequance nextval
    Paul Horth Expert
    Currently Being Moderated
    994229 wrote:
    i want to make continous numbering after deleting row
    No, that would not scale or work well in a multi-user environment.

    Anyway, you were asked what the business reason was. Why does the business think they need continuous numbering on the rows?
  • 8. Re: sequance nextval
    Peter Gjelstrup Guru
    Currently Being Moderated
    994229 wrote:
    i want to make continous numbering after deleting row
    Meaning in a table with 1.000.000.0000.001 rows, deleting one would mean potentially updating 1.000.000.0000.000 ?

    Best of luck, you are going down the worst possible track.
    Peter
  • 9. Re: sequance nextval
    997232 Newbie
    Currently Being Moderated
    just my idea no business reasons but you right good example with 10000000000000000000000 rows :)
  • 10. Re: sequance nextval
    EdStevens Guru
    Currently Being Moderated
    994229 wrote:
    just my idea no business reasons but you right good example with 10000000000000000000000 rows :)
    Plus, with your cache of 10000, you stand to loose forever those 10000 numbers for any number of reasons, before they are even used.
  • 11. Re: sequance nextval
    rp0428 Guru
    Currently Being Moderated
    >
    i want to make continous numbering after deleting row
    >
    Tom Kyte, Oracle ACE and noted author, said it best over 12 years ago:

    SEQUENCES WILL NEVER GENERATE A GAP FREE SEQUENCE OF NUMBERS!

    See this AskTom is from 2001. It is just as valid now as it was then
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441
    >
    Sequences will never generate a gap free sequence of numbers.

    If someone calls seq.nextval and doesn't insert the value somewhere (or does insert it
    and later rolls back their transaction) that number is GONE.

    Sequences are database objects and may be aged out of the shared pool like everything
    else -- it doesn't take a database shutdown to produce a gap.

    You should never count on a sequence generating anything even close to a gap free
    sequence of numbers.
  • 12. Re: sequance nextval
    chris227 Guru
    Currently Being Moderated
    It seems that you want that ordering by time of insert.
    You may consider to save the time of insertion in a separate column, creating a view on it with a an column with the rwo number like
    row_number() over (order by insert_date)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points