Oracle implementation self-increment columns

Start by creating a sequence (sequence) that implements the self-increment

--Create sequence Create sequence T_user_seqminvalue 1--Minimum value: 1nomaxvalue--no maximum value start with 1--starting from 1 increment by 1--each time Increment 1nocache--Do not use cache (can also be set to cache N, the difference is shown in the last note of this article) order;

Create a trigger that uses the Sequence:t_user_seq

--Create trigger Create or replace trigger t_user_trigger--triggers before insert on t_userfor before inserting new data into table T_user each rowbegin Select T_user_seq.nextval--increment field: user_id into:new.user_id from sys.dual; end;

Finally execute several INSERT statements to test.

Note:

If you specify the cache value, Oracle can pre-place some sequence in memory so that it accesses faster. After the cache is finished, Oracle automatically takes another set to the cache. Using the cache may jump, such as the database suddenly abnormal down (shutdown abort), the cache sequence will be lost. For example: For example, in your sequence cache 100, when you sequence to 90 o'clock suddenly power off, then after you restart the database, the value of sequence will start from 101.

If you specify the NoCache value, Oracle does not pre-store sequence in memory, and of course this avoids the loss of sequence that the database is not properly down. However, there are some problems: the creation of NoCache sequence is prone to the row cache lock wait event when high concurrent access occurs, mainly because the dictionary information in Rowcache needs to be modified each time the Nextval is acquired. Using NoCache sequence also causes the following problems:
Because a commit is required each time the dictionary information is modified, it may cause log file sync to wait, NoCache sequence in a RAC environment, an index created from a sequence-generated column causes a large number of index block contention between instances
Avoid creating NoCache sequence based on the above problems.

Take a look at sequence related protection mechanisms:
Row Cache Lock: Occurs when a data dictionary needs to be modified in the case of a sequence.nextval call, corresponding to the row cache lock event
SQ Lock: Occurs when fetching sequence.nextval on memory cache (not rowcache), corresponding to Enq:sq-contention event
SV Lock: Occurs when obtaining the sequence.nextval of the Cache+order property in a RAC environment, corresponding to the DFS lock handle event

What is the use of NoCache when using the cache?

I personally feel that I should try to use the cache, because many of the current database is running in high concurrency, first this can be performance, and will not produce a row cache lock wait event. Some people may worry that the database is not normal down will produce serial number discontinuity, but this is also very few cases. Of course, if your business requirements are not to produce a discontinuous serial number, then use Nochache.

Reference: http://blog.csdn.net/duanning397/article/details/7670302

Oracle implementation self-increment columns