Kingbasees database administrator's Guide -- 15.2 Manage sequence

Catalog

15.2.1.  About sequence

15.2.2.  Create sequence

15.2.3.  Modify the sequence

15.2.4.  Usage sequence

15.2.5.  Cache serial number

15.2.6.  Delete sequence

You can perform the creation sequence 、 Modify the sequence 、 Tasks such as using sequences and deleting sequences .

• About sequence

A sequence is a database object , Used to automatically generate a set of rules ( Increase or decrease ) Serial numbers that vary continuously , It is generally applied to the primary key column of a table .

• Create sequence

Use CREATE SEQUENCE Statement to create a sequence .

• Modify the sequence

Use ALTER SEQUENCE Statement modification sequence .

• Usage sequence

For users , The available resources in the sequence are serial numbers , Serial numbers can be accessed by multiple users and incremented or decremented .

• Query of sequence information

Sequence as a database object , The relevant information is stored in the data dictionary .

• Delete sequence

If a sequence is no longer needed , have access to DROP SEQUENCE Statement to delete the sequence .

Parent theme ：  Manage views 、 Sequences and synonyms

15.2.1.  About sequence

A sequence is a database object , Used to automatically generate a set of rules ( Increase or decrease ) Serial numbers that vary continuously .

Sequences are generally applied to primary key columns of tables , Can be used by multiple users , You can also generate primary keys for multiple tables . imagine , If there is no sequence , You can only write programs to generate sequential values , In the case of using sequences , When inserting data into a table , The primary key column is directly assigned with the serial number , Ensure that the primary key column has no duplicate values , You can also get more reliable primary key values .

Parent theme ：  Manage sequence

15.2.2.  Create sequence

Use CREATE SEQUENCE Statement to create a sequence .

To create a sequence in a pattern , You must have CREATE SEQUENCE System permissions . To create a sequence in another user's schema , Must possess CREATE ANY SEQUENCE Authority . for example , The following statement creates a sequence , Used to student Tabular stu_nmb The primary key column generates a four digit student number :

CREATE SEQUENCE stu_sequence
      INCREMENT BY 1
      START WITH 1000
      MINVALUE 1000
      MAXVALUE 9999
      CACHE 100
      NOCYCLE;

As can be seen from the above example , You can control the sequence through various option parameters , Indicates whether the sequence is ascending or descending 、 Starting value of serial number (START WITH)、 minimum value (MINVALUE) And maximum (MAXVALUE) And the increasing range between sequence values (INCREMENT BY) wait .

among NOCYCLE Option means that the sequence will not generate more values after reaching the maximum or minimum value . If you want the serial number to be recycled , Please use CYCLE Options .

CACHE Clause preallocation 100 Serial numbers are stored in memory . Every time the user uses the sequence , You need to query the sequence once , When stored in memory, it can greatly speed up the acquisition of serial numbers , When the last serial number in the cache is used , The database will read another set of serial numbers into the cache .

If you choose to cache a set of serial numbers , Then the database may skip this group of serial numbers . for example , When the instance closes abnormally ( for example , When an instance fails or issues SHUTDOWN ABORT When the sentence is ), Cached but unused serial numbers will be lost . Besides , Used but unsaved serial numbers will also be lost .

Parent theme ：  Manage sequence

15.2.3.  Modify the sequence

Use ALTER SEQUENCE Statement modification sequence .

To change the sequence , Your schema must contain this sequence , You must have ALTER Object permissions , Or it must have ALTER ANY SEQUENCE System permissions . You can modify the sequence by modifying the definition parameters of the generated value of the sequence .

The following example changes stu_sequence The growth rate of the sequence is 2, The serial number of recycling and the number of caches are changed to 20:

ALTER SEQUENCE stu_sequence
    INCREMENT BY 2
    CYCLE
    CACHE 20;

meanwhile , You should pay attention to the rationality of the value when modifying the sequence , For example, the following statement is obviously unreasonable , Because the starting value (START WITH) yes 1000, The following modification attempts to change the minimum value to 2000：

ALTER SEQUENCE stu_sequence
        INCREMENT BY 2
    MINVALUE 2000
    MAXVALUE 8000
    CYCLE
    CACHE 20;

Parent theme ：  Manage sequence

15.2.4.  Usage sequence

For users , The available resources in the sequence are serial numbers , Serial numbers can be accessed by multiple users and incremented or decremented .

To use sequences , Your schema must contain this sequence , Or you must have been granted another user sequence SELECT Object permissions . Once the sequence is created , The sequence can be used by multiple users ( Have SELECT Object permissions ) Access and increment , Without waiting .

• Use NEXTVAL Generate serial number

Once the sequence is generated , The user can be in SQL Used in statement CURRVAL and NEXTVAL Pseudo column to access the value of the sequence .

• Use CURRVAL Serial number

To use the current sequence value , Please be there. SQL Reference... In a statement seq_name.CURRVAL.

Parent theme ：  Manage sequence

15.2.4.1.  Use NEXTVAL Generate serial number

Once the sequence is created , Users can use SQL Statement from CURRVAL and NEXTVAL Get the value of the sequence in the pseudo column .

CURRVAL and NEXTVAL Pseudo columns are like pointers to sequences ,NEXTVAL Every time you get a value , The pointer moves back one position , This can be used CURRVAL Get the current serial number . namely , Its significance is as follows ：

• 1. CURRVAL Returns the current sequence value ;

• 1. NEXTVAL Returns the next available serial number .

As described above ,NEXTVAL and CURRVAL Not reserved words or keywords , Can be in SELECT、INSERT or UPDATE etc. SQL Used as a pseudo column name in the statement . When using the serial number for the first time , Must visit first NEXTVAL Pseudo column . To generate and use serial numbers , Please quote SQL Statement NEXTVAL, The following example , From pseudo table dual Check the serial number in ：

SELECT stu_sequence.NEXTVAL FROM dual;

The execution result of this statement is ：

NEXTVAL
----------
2000

for example , Suppose there are new student registrations , The serial number can be directly referenced in the value list :

INSERT INTO student VALUES (stu_sequence.NEXTVAL, ' Zhang Li ',18,' Senior high school grade two ',2,'[email protected]',SYSDATE);

When you need to update the serial number of a classmate , Can be in UPDATE Of the statement SET Reference serial number in Clause . for example :

UPDATE student SET student_id = stu_sequence.NEXTVAL WHERE student_id = 2022;

Parent theme ：  Usage sequence

15.2.4.2.  Use CURRVAL Serial number

To use the current sequence value , Please be there. SQL Reference... In a statement seq_name.CURRVAL.

Use CURRVAL Before ,seq_name.NEXTVAL Must have been referenced in the user session ( In current or previous transactions ) At least once . You can quote as many times as you need CURRVAL, Include multiple references in the same statement . In reference to NEXTVAL Before , The next serial number will not be generated . Continue with the previous example , You will insert new student data , To register :

INSERT INTO student VALUES (stu_sequence.CURRVAL, ' Liu Yi ',17,' First year of high school ',4,'[email protected]',SYSDATE);
INSERT INTO student VALUES (stu_sequence.CURRVAL, ' Zhao Wen ',19,' First year of high school ',5,'[email protected]',SYSDATE);

Suppose that given in the previous section INSERT Statement generates a new sequence number 2022, Then the two lines inserted in the statement in this section are inserted with sequence number 2022 The line of . Of course , If quoted CURRVAL The column of defines the primary key constraint , The reference of this method will restrict the error .

Parent theme ：  Usage sequence

15.2.5.  Cache serial number

Caching serial numbers can improve access efficiency .

• About cache serial numbers

Accessing serial numbers in the sequence cache is faster than reading them from disk .

• Number of entries in the sequence cache

When an application accesses a sequence in the sequence cache , The serial number will be read quickly .

• Number of serial numbers in the cache

When the sequence is read into the cache , The specified number of serial numbers has been generated and stored in the cache .

Parent theme ：  Manage sequence

15.2.5.1.  About cache serial numbers

Accessing serial numbers in the sequence cache is faster than reading them from disk . The sequence cache consists of entries , Each entry can hold multiple serial numbers of a single sequence .

Follow these guidelines to quickly access all serial numbers :

• Please make sure that enough serial numbers are cached in the sequence , Ensure that applications use all sequences concurrently .

• In case of high concurrency, the number of serial numbers in the cache should be increased .

Parent theme :  Cache serial number

15.2.5.2.  Number of entries in the sequence cache

When an application accesses a sequence in the sequence cache , The serial number in the cache will be read quickly .

If the sequence accessed by the application is not in the cache , Then before using the serial number , The sequence must first be read from disk into the cache . If an application uses many sequences concurrently , Then the sequence cache may not be large enough , Cannot accommodate all sequences . under these circumstances , Accessing the serial number may often require reading the disk . For quick access to all sequences , Please make sure there are enough serial numbers in the cache , This ensures that applications use all sequences concurrently .

Parent theme :  Cache serial number

15.2.5.3.  Number of serial numbers in the cache

When the sequence is read into the cache , The specified number of serial numbers has been generated and stored in the cache .

Then you can quickly access these values . The number of serial numbers stored in the cache is determined by CREATE SEQUENCE Statement CACHE Parameter determination , The default value for this parameter is 1. CREATE SEQUENCE Sentence creation seq_test2 Sequence , Of this sequence 50 Values are stored in SEQUENCE In cache :

CREATE SEQUENCE seq_test2
    CACHE 50;

Parent theme :  Cache serial number

15.2.6.  Delete sequence

If a sequence is no longer needed , have access to DROP SEQUENCE Statement to delete the sequence .

Users can delete any series they create . To delete the sequence of other users , Must possess DROP ANY SEQUENCE System permissions . After the sequence is deleted , Relevant information is no longer retained in the data dictionary .

for example , The following statement deletes the stu_sequence Sequence :

DROP SEQUENCE stu_sequence;

Parent theme ：  Manage sequence