ORACLE Sequence Self-growth

Sequence is a sequence of numbers automatically added by a database system in accordance with certain rules. This sequence is generally used as the surrogate primary key (because it is not duplicated) and has no other meaning.

Sequence is the characteristic of database system, some databases have sequence, some are not. For example, Oracle, DB2, PostgreSQL database has sequence,mysql, SQL Server, Sybase and other databases do not sequence.

According to my own understanding, sequence is a special data in the table arithmetic progression, the table is controlled by the database system, any time the database system can be based on the current record number size plus step to get to the table the next record should be how much, this table does not have practical significance, often used for the key use, very good, Oh, but very depressed each database manufacturer urine not to a pot-each has a set of sequence definition and operation. Here I make a comparison and summary of the definition and operation of the common three kinds of sequence, so that I can see it later.
I. Definition of sequence

Defines a seq_test with a minimum value of 1, a maximum value of 99999999999999999, starting at 1, an incremental step of 1, and a cyclic sort sequence with a cache of 20.

How Oracle is defined:
Create sequence Seq_test
MinValue 1
MaxValue 99999999999999999
Start with 1
Increment by 1
Cache 20
Cycle
Order

DB2 's wording:
Create sequence Seq_test
As bigint
Start with 20000
Increment by 1
MinValue 10000
MaxValue 99999999999999999
Cycle
Cache 20
Order

PostgreSQL's wording:
Create sequence Seq_test
Increment by 1
MinValue 10000
MaxValue 99999999999999999
Start 20000
Cache 20
Cycle

Second, Oracle, DB2, PostgreSQL database sequence value reference parameters are: Currval, Nextval, respectively, the current value and the next value.

The values of Nextval are obtained from the sequence of three databases, respectively.

In Oracle: Seq_test.nextval
For example: select Seq_test.nextval from dual;
DB2 in: Nextval for Seq_topicms
For example: Values nextval for seq_test;
PostgreSQL: Nextval (seq_test)
For example: Select Nextval (seq_test);
The difference and connection between sequence and indentity

The basic function of sequence and indentity is similar. Can generate a sequence of self-increment numbers.
Sequence is an object in the database system that can be used throughout the database and has no relation to the table; indentity is only specified on a column in the table, and the scope is the table.

A brief introduction to ORACLE sequence
In Oracle, sequence is the so-called serial number, which is automatically incremented each time it is taken, and is typically used where serial numbers need to be sorted.

1. Create Sequence

You first have to have create sequence or create any sequence permissions,
CREATE SEQUENCE emp_sequence
INCREMENT by 1-add a few each time
Start with 1-counting starting from 1
nomaxvalue-do not set the maximum value
Nocycle-always accumulates, does not circulate
CACHE 10;
Once you've defined emp_sequence, you can use Currval,nextval
Currval= returns the current value of the sequence
Nextval= increases the value of sequence and then returns the sequence value

Like what:
Emp_sequence. Currval
Emp_sequence. Nextval

Places where you can use sequence:
-SELECT statements that do not contain subqueries, snapshot, and view
-INSERT statement in subqueries
-In the values of the Nsert statement
-In the set of UPDATE

You can see the following example:
INSERT into EMP VALUES (empseq.nextval, ' LEWIS ', ' clerk ', 7902, Sysdate, N, NULL, 20);
SELECT Empseq.currval from DUAL;
However, it is important to note that:

– The first nextval returns the initial value, and the subsequent nextval automatically increases the increment by value that you define, and then returns the incremented value. Currval always returns the value of the current sequence, but the currval is not used until the first nextval is initialized, otherwise an error occurs. Once nextval will increase the value of sequence once, so if you use Nextval in a different SQL statement, the value is not the same.

– 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. So you can use NoCache to prevent this when the create sequence.

2. Alter Sequence

You are either the owner of the SEQUENCE or have alter any SEQUENCE permission to change the SEQUENCE. You can alter all sequence parameters except start to. If you want to change the start value, you must drop sequence and then re-create.
An example of Alter sequence
ALTER SEQUENCE emp_sequence
INCREMENT by 10
MAXVALUE 10000
Cycle-to 10000 and start from the beginning.
NOCACHE;

Initialization parameters that affect sequence:
Sequence_cache_entries = Sets the number of SEQUENCE that can be simultaneously CACHE.
Can be very simple drop Sequence
DROP SEQUENCE Order_seq;

The following details the limitations of Nextval and currval usage and sequence usage
Using Nextval

The first time a sequence is accessed, the reference sequence. You must first refer to sequence before currval. Nextval. For the first time, Nextval is referenced, returning the initial value of the sequence. Each subsequent reference to Nextval, increments the sequence value with the defined step and returns the new value of the sequence after the increment.

You can add only one time to a given sequence in an SQL statement. Even if sequence is specified more than once in a single statement. Nextval, the sequence is also added only once, so each sequence. Nextval appears in the same SQL statement that returns the same value. Each sequence except in the same statement that occurs more than once in this case. The nextval expression increases the sequence regardless of whether the current transaction is subsequently committed or rolled back. If sequence is specified in a transaction that is eventually rolled back. Nextval, some sequence numbers may be skipped.

As in PL/sql:
The value of query Nextval equals 151
Select Cheng.nextval from test1234
Execute INSERT statement
INSERT into test1234 values (cheng.nextval, ' BB ', 22);
The value of Nextval after commit or rollback is increased to 153
Using Currval

Any reference to Currval returns the current value of the specified sequence, which is the value returned by the last reference to Nextval. After generating a new value with Nextval, you can continue to use Currval to access the value, regardless of whether another user increments the sequence. If sequence. Currval and sequence. Nextval are present in an SQL statement, the sequence is incremented only once. In this case, each sequence. Currval and sequence. The Nextval expression returns the same value, regardless of sequence in the statement. The order of Currval and Sequence.nextval.

As in PL/sql:
Select Cheng.nextval,cheng.currval from test1234
The values for Nextval and Currval are all 160.
Concurrent access for sequences

A sequence always generates unique values in the database, even when multiple users refer to the same sequence concurrently without perceptible waiting or locking. When multiple users use Nextval to grow a sequence, each user generates a unique value that is not visible to other users. When multiple users increase the same sequence concurrently, the values that each user sees are different. For example, a user might generate a set of values from a sequence, such as 11, 14, 16, and 18, while another user generates values 12, 13, 15, and 17 from the same sequence concurrently.
Restrictions used by sequence

Nextval and Currval are only valid in SQL statements and are not directly valid in SPL statements. (But SQL statements using Nextval and Currval are available for SPL routines) The following restrictions apply to these operators in SQL statements:
[1] in the CREATE table or ALTER table statement, nextval or Currval cannot be specified in the following contexts:
In the DEFAULT clause.
In the CHECK constraint.

[2] in the SELECT statement, Nextval or Currval cannot be specified in the following contexts:
The DISTINCT keyword is used in the projection list.
In the Where, GROUP by, or ORDER by clause.
In the subquery.
When the union operator is combined with a SELECT statement.

[3] Nextval or Currval cannot be specified in any of the following contexts:
In a segmented storage expression
In a reference to a remote sequence object in another database.
Implement a feature like auto-increment ID in Oracle

We often use a system-assigned ID as our primary key when designing a database, but there is no such feature in Oracle, and we can automatically add ID by taking the following functions.

1. First create sequence
Create sequence Seqmax increment by 1
2. How to use
Select Seqmax.nextval ID from dual
You get the same feature ID value as the MS SQL Auto-increment ID.

Copy Source reference: http://www.a18zhizao.cn/y2008/617_since-the-growth-oracle-sequence.html

http://blog.csdn.net/aqszhuaihuai/article/details/4100793

ORACLE Sequence Self-growth