how to increment an integer column?

[silver40]

I have created a table with an 'ID' column that is 'NOT NULL' and is of the INTEGER datatype.

I want this column to increment by 1 for every row that it contains.

So if I have 4 rows in my table, the ID colum will look like this :

ID
----
1
2
3
4

I'm using this syntax, but I'm getting an error.

SQL> Create or Replace Sequence ID

Start with 1

Increment by 1

NoMinValue

NoMaxValue

NoCycle

NoCache;

ERROR: ORA-00922: missing or invalid option

Can someone help me?

[jmodic]

Originally posted by silver40

SQL> Create or Replace Sequence ID
......

Change this line to:

SQL> Create Sequence ID

and you'll be fine. You can't use OR REPLACE option with sequences.

BTW, I hope you are aware that it is almost unavoidable to have "gaps" (mising numbers in your ID column) when using sequences.

[silver40]

It works !!

However, I'm not sure what you mean by having gaps in my ID column.

I don't understand how this could happen ?

Also I need to know if I can specify this sequence to one table.

The sequence command affected all of my tables.

All these tables are in the same database, but I only want some of these tables to have an incrementing 'ID' column.

Also, when I loaded some data into a table and then deleted it, the ID values were still cached.

For example I would load a table with 4 rows of data, and my ID column would look like this :

ID
----
1
2
3
4

(The value I specify for the ID column is 'ID.NextVal' when inserting the data.)


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


My only thought for why this is happening is that the ID column is being cached.

I don't understand why because I clearly specified 'NoCache' when creating the sequence.

please help !!!

[jmodic]

Originally posted by silver40
It works !!

However, I'm not sure what you mean by having gaps in my ID column.

I don't understand how this could happen ?

Let's say your ID sequence is fresh, starting with 1. You execute the following sequence of statments:

1.insert into my_table(c1) values(ID.NEXTVAL);
commit;
2.insert into my_table(c1) values(ID.NEXTVAL);
rollback;
3.insert into my_table(c1) values(ID.NEXTVAL);
commit;

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

[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).

[reydp]

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

[jmodic]

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.