Oracle Database sequence Detailed

Objective:

People who have done web development basically know that the primary key values in a database table are sometimes used as numeric types and are self-increasing. This makes it easy to use tools to create tables in MySQL and SQL Server. However, there is no way to set the self-increment in Oracle, and in general we use sequences and triggers to implement the function of primary key self-increment. The following article mainly describes the sequence.

First, what is a sequence

Sequence: Sequence is a database object provided by Oracle to produce a series of unique numbers. Since there is no way to set the self-increment in Oracle, we mainly use sequences in the Oracle database to realize the function of primary key self-increment.

Second, how to create a sequence

CREATE SEQUENCE sequence //创建序列名称[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue[{MAXVALUE n | NOMAXVALUE}] //最大值  [{MINVALUE n | NOMINVALUE}] //最小值[{CYCLE | NOCYCLE}] //循环/不循环[{CACHE n | NOCACHE}];//分配并存入到内存中

Third, how to use the sequence

After the sequence is created, you can use the nextval of the sequence to get the next value of the sequence, using Currval to view the current value. First use must use Nextval to produce a value before you can view it using Currval.

//序列调用 产生一个新的序列select seq_test.nextval from dual//查看当前序列的值select seq_test.currval from dual

If you use Currval directly to access the sequence for the first time, the following error is reported:

Iv. Creating an example of a sequence

4.1 Simplest sequence Example

CREATE SEQUENCE seq_test;

Executing this CREATE statement produces a sequence object named Seq_test in the database, and the default values are used by default for all other parameters. View the generated source code as follows:

-- Create sequence create sequence SEQ_TESTminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;

When you call seq_test this sequence, it will generate a continuous increment from 1 to 999999999999999999999999999, starting with 1, each increment is 1, not looping. After the maximum value is generated, it cannot be used. The cache is generated by default, with a number of 20 caches per cache. About caching we'll talk later.

4.2 Creating a non-circular sequence with the largest value

create sequence seq_test1increment by 1start with 10maxvalue 300minvalue 5;

This sequence, although setting a minimum value of 5, does not produce a value below 10 because the start value is 10 and does not loop. Note that the starting value of the sequence cannot be less than the minimum value, or the sequence will be created with an error. Let's change the above code to read as follows:

create sequence seq_test1increment by 1start with 10maxvalue 300minvalue 11;

Perform an error such as:

4.3 Creating a loop sequence with the largest value

create sequence seq_test2increment by 1start with 10maxvalue 300 minvalue 5cycle ;

When we perform a sequence extraction to a maximum value of 300, the sequence starts again from the minimum value of 5. The first time this sequence is generated from a start value.

It is important to note that if we create a loop sequence, we have to set the maximum value, otherwise we will get an error:

create sequence seq_test2increment by 1start with 10minvalue 5cycle ;

Perform the error as shown:

V. Using a sequence with a cache

Using the cache while creating a sequence can improve performance, especially in the case of high concurrency, performance gains for the database are good. But using a cache can have a sign-off phenomenon, and if your business requirements sequence produces values that must be contiguous, you can only use NoCache.

Let's take a look at the role of the cache parameter. Cache, which is useful for caching a specified number of sequence values. For example, if you set the cache to 20, Oracle will take the next sequence value directly from the cache if the cached sequence value in the cache is not available (for example, if the sequence value is exhausted or is manually emptied), then Oracle will again produce the Nextval 20 sequence values and place the cache for use, which helps to increase the speed of sequence values.

Let's use a case to illustrate the role of caching:

--创建一个带缓存的序列create sequence SEQ_CACHEminvalue 1maxvalue 1000start with 1increment by 1cache 20;

Executing Seq_cache.nextval returns the first value of 1. Call Seq_cache.currval to view the current value of 1.

When we first called Nextval, because the cache number was set to 20, the sequence would generate 20 values in the cache at a time. When we call Nextval again, we are actually taking the value from the cache. Let's test it if we now empty the cache and call Nextval.

-- 清空 cache 中缓存的序列值alter system flush shared_pool;-- 再次调用nextval获取序列值select seq_cache.nextval from dual;

found that the obtained value is 21 instead of 2. Because the value in the cache is emptied, the system automatically acquires 20 new consecutive values in the cache.

We will now execute the Nextval four times and get the current value of 25. At this point we empty the cache again and then call Nextval again to get the sequence.

-- 清空 cache 中缓存的序列值alter system flush shared_pool;-- 再次调用nextval获取序列值select seq_cache.nextval from dual;

We will get the current value of 41. Why is it? Because each time Oracle gets a value of 20, it starts with the last obtained maximum value, not from the current value! The use of caching generates a risk of non-connectivity, and if the system is out of order or an Oracle restart, the system empties the cached data and retrieves the value of the corresponding cache setting when the Nextval is called.

Let's look at a case with a cache:

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 300minvalue 10cyclecache 50;

An error follows after execution:

Why is there such a mistake?

The value of our cache setting is 50, and the maximum value is 300, so why is this information prompted? In fact, our cache is 50, but each time we grow the value is 10. So the number of 50 cache fetches is 500 (50*10), the maximum value of each cycle is 300, so we are prompted that the cache value must be less than the maximum value of a cycle.

We will modify the code as follows:

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 500minvalue 10cyclecache 50;

The discovery still shows an error as shown in:

Why is that? The maximum value of our one-cycle has been set to 500, why is there such an error? This is because there is also a minvalue, between MinValue and MaxValue is 490 numbers, that is, a loop can fetch 490, but our cache is 500.

We will modify the code as follows:

create sequence SEQ_CACHE1increment by 10start with 10maxvalue 500minvalue 9cyclecache 50;

Discovery created sequence succeeded. Setting the cache value when creating a sequence we have a basic formula that requires:

Maximum-minimum value >= (cache value-1) * Value per loop

There is no problem as long as the cached value setting for this formula is satisfied.

Summarize:

Through the above we found that the use of the sequence has several basic constraints, summed up the following several:

1, the sequence must first call Nextval to get a sequence value to use Currval to view the current value

2. The starting value of the sequence cannot be less than the minimum value

3. To create a loop sequence, you must set the maximum value

4. If you create a sequence with a cache, the cached value must satisfy the constraint formula: maximum-minimum value >= (cache value-1) * Value per loop

The above is a detailed description of the sequence of Oracle database, we hope to understand and master the use of the sequence can be useful.

Oracle Database sequence Detailed