0

When I created a sequence for a table article, it's started from 17 not 1

CREATE SEQUENCE seq_article START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER auto_article BEFORE insert ON article
FOR EACH ROW
BEGIN
  SELECT seq_article.NEXTVAL INTO :NEW.id_article FROM dual;
END;
/

I tried to delete all rows and creat other data, this time it's started from 19. How can I fix that?

mpluse
  • 1,857
  • 6
  • 18
  • 25
  • Possible Duplicate: http://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle – sgeddes Apr 20 '13 at 21:46

1 Answers1

1

I'm not sure that I understand the problem.

  • A sequence generates unique values. Unless you set the sequence to CYCLE and you exceed the MAXVALUE (not realistically possible given the definition you posted) or you manually reset the sequence (say, by setting the INCREMENT BY to -16, fetching a nextval, and then setting the INCREMENT BY back to 1), it won't ever generate a value of 1 a second time. Deleting the data has no impact on the next id_article that will be generated.
  • A sequence-generated column will have gaps. Whether because the sequence cache gets aged out of the shared pool or because a transaction was rolled back, not every value will end up in the table. If you really need gap-free values, you cannot use a sequence. Of course, that means that you would have to serialize INSERT operations which will massively decrease the scalability of your application.
Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Well, the problem it increments by 1 but generates automatically the first number! – mpluse Apr 20 '13 at 21:54
  • @Khan, are you trying to do something meaningful with these numbers? Generally, all you want is uniqueness. – Dan Bracuk Apr 20 '13 at 22:00
  • @Khan - I'm still not understanding the problem. The first call to `nextval` did not return 17, it returned 1 if the sequence definition is what you say it is. It is entirely possible that the value of 1 was somehow lost because the session that retrieved that value didn't persist it to the database (perhaps there was an error, for example), because the session rolled back the `INSERT`, or for some other reason. Since you don't use a sequence when you need gap-free values, though, it shouldn't matter if there is no row with an `id_article` of 12. – Justin Cave Apr 20 '13 at 22:03
  • @Khan - But what? Did you intend that comment to end where it ended? – Justin Cave Apr 21 '13 at 01:57
  • But it's not the case! I can't understand why it returns a random number for the first row – mpluse Apr 21 '13 at 01:58
  • 1
    @Khan - That seems highly unlikely (and I assume that you really don't mean random). If I run the code you posted, the first `nextval` will be 1. If you are seeing some other behavior, you must be doing something that you haven't mentioned here. – Justin Cave Apr 21 '13 at 02:00