Re: How to increased by exactly 1 ?

My first question is why you want to do this? If you need to identify the data in numeric order with no missing values I would look to populate the this value at the time of use by ordering the data on a sequence generated value and then assigning a consecutive numeric label (rownum).

I am not sure what you want can even be done with 100% certainty. You have to consider the Oracle read consistency model where writers do not block readers. I though of trying to find the max value then performing a select for update on it with nowait specified, inserting the new row, replacing the selected row, and commiting. Other sessions would error off due to the nowait condition, burn some centi-seconds, refind the max value, retry the select for update on the new max value, etc.... If testing with several concurrent update attempts shows that two sessions can still find the same maxvalue then single threading access to the table via dbms_lock would be required. This is not efficient nor scalable.

HTH -- Mark D Powell -- Received on Fri Jan 21 2005 - 09:44:22 CST