If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

If you query your table you'll find out that inserted c1 values are 1 and 3, while value 2 is missing, because you rolled back that transaction. You'll never again get number 2 back from that sequence! So when you rollback a transaction, all the sequence values involved in that transaction are "lost", they are not "returned to the sequence". That's why you'll have gaps in sequence-populated columns.

Now for your other question. Your sequence values are not cached anywhere because you used NOCACHE option when creating it. That means that whenever you select a new value from the sequence oracle must get that value directly from the database dictionary and it must also save information of the last used sequence number into the dictionary with every "fetch" from the sequence.

Note also that sequence is a totaly independent object in a database, it is not dependant on any table or any other object. So if you delete your for rows from your table1 it doesn't mean that those id values should be returned to the sequence. So it is totaly normal that you've got number 5 into your second table because this was the next available number from the sequence ID. Once you've fetched a value from a sequence that value is lost and will never be returned again with SEQ.NEXTVALUE (unles your sequence cycles).

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?

So, the best way to increment ID (without gaps) would be to create a function that adds 1 to the last ID (max id selected from table) and use this function in the insert statement. The (first row has to be initialized with 1).

-------------------------------
Then I would delete all these rows and populate 1 row of data into ANOTHER DIFFERENT table.

I still use value 'ID.NextVal' when inserting the data.

My ID column on this other table should read :

ID
----
1

but, it reads :
ID
----
5
-------------------------------

If your intension is to use the same id number both for the same table then you should not use the sequence ID.NextVal anymore. As what jmodic says sequence is totally independent from other objects, so the current value of the sequence is the last value acquired after you issue the nextval.

Your option is to use that ID sequence both for two tables before nextval is issued:
insert into my_table(c1) values(ID.NEXTVAL);
insert into table2(c1) values(ID.CURRVAL);
commit;

Originally posted by Raminder So, the best way to increment ID (without gaps) would be to create a function that adds 1 to the last ID (max id selected from table) and use this function in the insert statement. The (first row has to be initialized with 1).

You have to be carefull with this aproach too! There are good chances that two concurent users will get the same max id from that table if they query it at approximately the same time, so they will end up with inserting the same id into the table!

A better way would be to create a separate one-column-one row table that will hold the current max value of the ID. Then you would use:

SELECT last_value + 1 FROM seq_table
FOR UPDATE OF last_value NOWAIT;

UPDATE seq_table SET last_value := last_value + 1;

The crutial thing here is to use SELCT FOR UPDATE NOWAIT you update the last_value with higher value. This prevents you to read current last_value from a table if someone else alreday have a lock on that row and at the same time it locks that row untill you commit your update of the last_value. However this kind of "manual sequence generator" imposes serialisation, noone can perform anything involvng any work with seq_table from the moment you lock it with SELECT FOR UPDATE until you commit or rollback your transaction. In an OLTP environment this kind of serialisation could be unacceptable.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?