ORACLE SEQUENCE
Oracle does not have a self-increment data type, such as the need to generate a business-independent primary key column or a unique constraint column, which can be implemented using the sequence sequence.
CREATE sequence Statement and parameter description:
Create sequence: Need to have create sequence or create any sequence permission,
CREATE SEQUENCE [schema.] Sequence
[{INCREMENT by | START with} integer
| {MAXVALUE Integer | Nomaxvalue}
| {MINVALUE Integer | Nominvalue}
| {CYCLE | Nocycle}
| {CACHE Integer | NOCACHE}
| {ORDER | Noorder}
];
CREATE sequence each parameter detailed:
The schema specifies the user's schema under which to create the sequence, which, if not specified, is created by default under the current user.
sequence specifies the name of the sequence sequence to create
Note: If you specify only the above parameters, a increment sequence starting from 1, incrementing by 1, with no maximum limit, is started.
If you want to create a sequence that has no constraints, increment the sequence: omit the maxvalue parameter or specify Nomaxvalue; decrement sequence: Omit the minvalue parameter or specify Nominvalue.
If you want to create a restricted sequence, increment the sequence: Specify the maxvalue parameter; decrement sequence: Specify the MinValue parameter. When the sequence reaches the limit, it will error:
If you want to create a restricted sequence that restarts after the limit is reached, specify MaxValue and MinValue, you also need to specify cycle. If you do not specify MinValue, the default is Nominvalue,
This value is 1.
INCREMENT by specifies the interval between serial numbers, which can be any positive or negative integer, but cannot be 0. This value has a maximum of 28 digits.
The absolute value must be smaller than the difference between MAXVALUE and MINVALUE (if the error is not in this interval: ora-04005:increment must be less than MAXVALUE minus MINVALUE).
If the value is negative, then the sequence drops. If the value is positive, the sequence rises. If this clause is omitted, the interval defaults to 1.
START with specifies the first sequence number to produce.
This clause starts an incrementing sequence that is greater than the minimum value, or starts a descending sequence that is less than its maximum value.
For an incrementing sequence, the default value is the minimum value in the sequence. For a descending sequence, the default value is the maximum value in the sequence. This integer value can be up to 28 digits.
This value is not related to the value of the restart when the maximum/minimum value of the limit is reached (such as when the increment sequence is created with the maximum minimum value specified and the cycle is specified, the sequence reaches the maximum value,
Starts with the minimum value, or, if the package minimum is not specified, the default is 1.
MAXVALUE Specifies the maximum value that a sequence can generate. This integer value can be up to 28 digits. MaxValue must be >=start with and must be greater than minvalue.
Nomaxvalue: Specifies that nomaxvalue indicates that the maximum value of the increment sequence is 10 27, or the descending sequence has a maximum value of-1. This is the default.
MINVALUE: Specifies the minimum value of the sequence. This integer value can be up to 28 digits. MinValue must be <=start with the value and must be less than MaxValue.
This discrepancy, reported: Ora-04006:start with cannot is less than MINVALUE. When this parameter is not specified, the default is 1.
Nominvalue: Specifies the nominvalue to represent the increment of the sequence with a minimum value of 1, and a decrement sequence of 26 squares with negative 10. This is the default.
Cycle: Specifies a loop that indicates the value that the sequence generates when it reaches its maximum or minimum value. When the increment sequence reaches the maximum value, the loop starts from the minimum value.
When the descending sequence reaches the minimum, the loop starts at the maximum value.
Nocycle: Specifies nocycle to indicate that the sequence cannot produce more values after reaching its maximum or minimum value. This is the default.
CACHE: Specifies how many values the database pre-allocates for a sequence to be placed in memory for faster access. This integer value can be up to 28 digits. The minimum value of this parameter is 2;
This value must be less than the number of cycles in a cycle (for example, from 1-100 is a loop, the cache is less than 100, or the value of a cache may be duplicated at a time.)
The error is: Ora-04013:number to CACHE must is less than one cycle).
The calculation formula is: (Ceil (Maxvalue-minvalue))/ABS (INCREMENT)
If the system fails, the unused cache value in memory is lost, causing the sequence to be discontinuous. Oracle recommends using cache in RAC to improve performance.
NOCACHE: Specifies that the sequence value is not pre-allocated. If you omit the cache and NoCache, the database caches 20 serial numbers by default.
Order: Only required when RAC is specified, order is specified to ensure that the serial number is generated because of a request. Useful when using serial numbers as a timestamp.
Noorder: This is the default.
Using sequences
The sequence generates a series of integer numbers. A sequence contains two "pseudo-columns", respectively, "Currval" and "Nextval", which can be used to get the current and next value of the sequence, respectively.
Although we specify that the sequence initial value is 1 when defined, it does not actually initialize the value. The sequence must be retrieved by retrieving the next value of the sequence, Nextval, before retrieving the current value of the sequence.
Initializes the operation. When Nextval is selected, the sequence is initialized to 1.
There are roughly the following effects on system performance when using sequence:
See: http://blog.itpub.net/17203031/viewspace-717042
The 1.seq$ base table is a data dictionary table sequence the record system. Each time the nextval is called, the update is called recursively and a commit seq$ the base table.
2. Update the seq$ base table and submit will produce redo log--hundreds of bytes, commit frequently cause lgwr pressure, too much redo log generation, resulting in LGWR pressure, recovery time and so on.
3. Multiple sessions using sequence may appear contention, wait for event row lock contention
For the Nocache/cache parameter:
NoCache: Each time you use Nextval, the seq$ base table is updated and commits.
Cache: The seq$ base table is updated and submitted only if the sequence is not retrieved until the serial number of the cache in memory is used.
For example, if the cache is set to 2000, the performance impact is much smaller than nocache when using sequence.
Therefore, in general, it is recommended to set a larger cache value for performance optimization. (The default is 20 when NoCache is not specified)
#####################################################
ORACLE Sequence Creation Example:
Create sequence Bys.test_seq
Increment by 3
Start with 5
MaxValue 18
MinValue 4
Cycle
Cache 4;
Create a sequence named Test_seq under the Bys user
Starting from 5, increase each time by 3, the maximum value is 18, the minimum value is 4
Allow reuse, cache 4 means that four serial numbers are cached, such as 5 8 11 14
Of course, in the experiment can also use the simplest: Create sequence bys.test_seq2; Other parameters do not write, use the system default haha
#####################################################
Examples of ORACLE sequence modifications and deletions:
ALTER SEQUENCE [schema.] Sequence
{INCREMENT by integer
| {MAXVALUE Integer | Nomaxvalue}
| {MINVALUE Integer | Nominvalue}
| {CYCLE | Nocycle}
| {CACHE Integer | NOCACHE}
| {ORDER | Noorder}
}
Three things to note when modifying:
If you want to make the sequence start with a different number, you can only delete the sequence rebuild.
If you change the value of increment by before using the Nextval initialization sequence, some serial numbers will be skipped. Workaround for skipping problems--delete rebuild
The new values for each parameter modified are still to be described in the introduction to the parameters of create sequence.
Example of changing the value of increment by before initializing a sequence of nextval:
Create sequence BYS.SEQ3
Increment by 3
Start with 5
MaxValue 18
Nominvalue
Cycle
Cache 4;
[email protected] bys3>alter sequence bys.seq3 increment by 5; --Pre-initialization modification
Sequence altered.
[Email protected] bys3>select seq3.nextval from dual; --It did skip some numbers during initialization.
Nextval
----------
7
[Email protected] bys3>select seq3.nextval from dual;
Nextval
----------
12
Example Modification statement:
ALTER SEQUENCE customers_seq MAXVALUE 1500;
ALTER SEQUENCE customers_seq CYCLE CACHE 5;
To delete a sequence statement:
DROP SEQUENCE [schema.] Sequence_name;
such as: [email protected] bys3>drop sequence BYS.SEQ2;
############################
ORACLE Sequence Use Example
See Official Document--http://docs.oracle.com/cd/b19306_01/server.102/b14200/pseudocolumns002.htm#i1006157
Common usage scenarios for sequences:
1 can be in the SELECT statement, CREATE TABLE ... As SELECT statement, CREATE materialized VIEW ... Used in as SELECT.
2 in the set of update, in the INSERT clause or values. A sequence can be accessed by multiple users at the same time without waiting or locking.
3 The first query is to use Nextval to return the initial value of the sequence.
4 Query the current serial number by: Currval, which returns the value returned by the last reference to Nextval.
5 querying the next sequence number with nextval--with this command, sequence increments the value specified by 1 or increment by, and then returns the sequence value
The query in this experiment:
[Email protected] bys3>select test_seq.currval from dual; ---not initialized with Nextval, this error is reported.
Select Test_seq.currval from dual
*
ERROR at line 1:
Ora-08002:sequence Test_seq. Currval is isn't yet defined in this session
[Email protected] bys3>select test_seq.nextval from dual; The first time you use Nextval, it shows the value specified by start with at creation time
Nextval
----------
5
[Email protected] bys3>select test_seq.currval from dual; Use Currval to find the current serial number---last reference to the value returned by Nextval
Currval
----------
5
[Email protected] bys3>select test_seq.nextval from dual; --Always executes nextval, observing how the sequence is recycled after it reaches the value specified MaxValue
Nextval
----------
17
[Email protected] bys3>select test_seq.nextval from dual; --The sequence reaches the value specified by MaxValue and returns the value specified by MinValue instead of start with. If MinValue is not specified or nominvalue is specified, 1 is returned.
Nextval
----------
4
[email protected] bys3>insert into test values (test_seq.nextval, ' seqtest '); --Using the INSERT statement to invoke the sequence
1 row created.
[Email protected] bys3>select * from test;
object_name STATUS
------------ -------
Ten Seqtest
[email protected] bys3>insert into test values (test_seq.currval, ' seqtest ');
1 row created.
[Email protected] bys3>select * from test;
object_name STATUS
------------ -------
Ten Seqtest
Ten Seqtest
[email protected] bys3>insert into Test (object_name) select Test_seq.nextval from dual; --Using the insert intonation with sequence
1 row created.
[Email protected] bys3>select * from test;
object_name STATUS
------------ -------
10 13
10 16
4
[Email protected] bys3>update test set status=test_seq.nextval; --Using the UPDATE statement to invoke the sequence
2 rows updated.
[Email protected] bys3>select * from test;
object_name STATUS
------------ -------
10 13
10 16
[Email protected] bys3>delete test where status=test_seq.currval; --delete cannot be used in sequence conditions
Delete Test where status=test_seq.currval
*
ERROR at line 1:
Ora-02287:sequence number not allowed here
Use the Solver to automatically insert an increment sequence for a table: the role of a----like self-increment field
The built-in code for the hair generator is:
Create or Replace Trigger tri_test_id
Before insert on test--test is a table name
For each row
Declare
NextID number;
Begin
IF:new.testid are NULL or:new.testid=0 then--departid are column names
Select Seq1.nextval--seq1 is the name of the pre-created sequence
into NextID from Sys.dual;
: New.testid:=nextid;
End If;
End tri_test_id;
