Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Risk in using sequence number?

Re: Risk in using sequence number?

From: Ricky Sanchez <rsanchez_at_more.net>
Date: Fri, 22 Feb 2002 17:19:40 GMT
Message-ID: <3C767DE8.C5AA56B9@more.net>


Leigh-

One risk is performance. If this is to scale well, you will have lots of primary key inserts into the same index leaf blocks. Since key values must be physically ordered, each increasing value goes into the block of the previously inserted key, until the block is full and you then start again in a new block. Under conditions of rapid concurrent inserts, you might see lots of 'buffer busy' waits on those blocks, and performance may suffer.

A way to work around the problem is to use 'reverse key' indexes. This type of key gets reversed in byte order, so effectively 1001, 1002, 1003... become 1001, 2001, 3001. The logical values of 1001, 1002, 1003 are preserved, but the stored values are changed so each insert tends to go to a different block and the insert load contention gets spread out. The only real drawback is any ordered select on that value cannot use a range scan. But, if you aren't going to be reading it very often, this technique might help.

Leigh Gold wrote:
>
> What is the risk involved in using sequence number to keep track of the last
> record in a table?
>
> We use a table to keep history of an event, everytime an event changes, we
> insert a row in this history table with a sequence number as its primary key
> and information about this event. We are basing our query on max(sequence)
> to track the last time this event occurred. For some reason, the developer
> refuses to use sysdate for this purpose.
>
> Is there any risk involved in using the method?
>
> Thanks,
>
> Leigh
Received on Fri Feb 22 2002 - 11:19:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US