一个关于序列参数的问题

weiping106205 2002-11-29 11:11:55

在建sequence时，有几个参数
Owner ，Minimum value，Maximum value，Initial value，Increment by ，Cache size，Cycle，Order
参数Cache size 到底起什么作用，将其设为10和20，对性能有多大
影响。
参数Cycle和Order具体又指什么，设为off,起什么作用。
另一个问题是，由于sequence直接建在数据库端，我在前端一直无法控制。例如，我前端用jsp,其中一个字段通过sequence产生，可是当用户在点击创建纪录时，又立即选择取消创建，而这时sequence已产生，如果下次创建时，该字段就不连续。
请教各位如何解决。
先谢了！

...全文

87 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

wanghai 2002-12-10

打赏
举报

An Oracle sequence is a named sequential number generator. Sequences are
often used for artificial keys or to order rows that otherwise have no order.
Like constraints, sequences exist only in the data dictionary. Sequences can
be configured to increase or decrease without bound or to repeat (cycle)
upon reaching a bounding value. Sequences are created with the CREATE
SEQUENCE statement. Figure 6.5 shows the syntax of the CREATE SEQUENCE
statement.

START WITH Defines the first number that the sequence will
generate. The default is one.

INCREMENT BY Defines the increase or decrease for subsequently
generated numbers. To specify a decreasing
sequence, use a negative INCREMENT BY.

MINVALUE The lowest number the sequence will generate.
This is the bounding value in a decreasing
sequence. The default MINVALUE is the keyword

NOMINVALUE, which translates to 1 for an
increasing sequence and to –1026 for a decreasing
sequence.

MAXVALUE The largest number that the sequence will
generate. This is the bounding value in the
default, increasing sequence. The default
MAXVALUE is the keyword NOMAXVALUE which
translates to 1027 for an increasing sequence and
to –1 for a decreasing sequence.

CYCLE Configures the sequence to repeat numbers after
reaching the bounding value.

NOCYCLE Configures the sequence to not repeat numbers
after reaching the bounding value. This is the
default. When you try to generate the
MAXVALUE+1, an exception will be raised.
CACHE Defines the size of the block of sequence
numbers held in memory. The default is 20.
NOCACHE Forces the data dictionary to be updated for each
sequence number generated, guaranteeing no
gaps in the generated numbers.

Doloris 2002-12-10

打赏
举报

Sequences avoid the performance problems associated with sequencing numbers generated by application triggers of the form:
DECLARE
TEMP_NO NUMBER;
BEGIN
LOCK TABLE PO_NUM IN EXCLUSIVE MODE NOWAIT;
SELECT MAX(PO_NUM)+1 INTO TEMP_NO FROM SALES ;
END;

If the application requires numbers that are exactly in sequence (e.g. 1,2,3....) then the trigger shown above may be your only recourse. Note however that if a statement that references a sequence is rolled back (canceled) that sequence number is lost.

weiping106205 2002-12-01

打赏
举报

Thank Doloris!
In the practical application,I specify the CACHE parameter 20.
I do not know how many the cache parameter should specify usually
and its influence .
In parallel mode,must I specify the ORDER option?
Another problem is that in my application, when customer insert a record, but he select giving up the new record at once before commit, unfortunately after he insert a record again ,there is a break in sequential numbering. please tell me how to solve the problem.
Thank you in advance.

Doloris 2002-12-01

打赏
举报

If the NOCACHE option is used during creation the numbers won't be cached in the SGA and sequential numbers will result. However, any numbers referenced by the NEXTVAL function will be lost if the transaction is rolled back before completion thus causing a break in sequential numbering.

CACHE will cache the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shutdown.

If you are using the Parallel Server option in parallel mode and you specify the ORDER option, sequence values are never cached, regardless of whether you specify the CACHE parameter or the NOCACHE option.

huangcaibing 2002-12-01