Re: Risk in using sequence number?

Remember that sequence numbers are used to guarantee uniqueness, not serialization. When you grab a sequence number from Oracle, Oracle guarantees that no one else will ever get that number. With multiple users getting numbers, it's possible that the user or process adding the last row will have a sequence number lower than previously posted transactions, unless you have some queuing mechanism wrapped around it.

Or use a trigger on insert to populate the column that you store the sequence in, rather than having that logic in your code; that would pretty much do what you want, as the number wouldn't be grabbled until a row was actually being inserted.

NOCACHE prevents 'holes' in your serialization, at a performance penalty; if all you care about is finding the last row for sure, caching is better, and of course NORECYCLE is an essential.

Huhhh. Wait a minute. You ought to have a teeny baby sized table with one row in it that has columns for, oh for the heck of it, SYSDATE and the sequence number, and have your trigger on Mr (Ms) Big table grab a sequence, populate into your column there before the row is inserted, and also do an UPDATE on baby table with the sequence number and sysdate.

If the sequence number column on Mr Big is indexed, then getting at the last row will be eased by not having to use the max() function that at least used to force a full table scan; you'd just get the last row with a join operation with the other table. And if all you wanted to know what the last record number was, look in Baby Table for it.

Things like max(), min(), LIKE, WHERE...IN (x,y,z) can be the devil dealing with sizeable tables.

RSH. "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:3koa7u0fh6uhhs69c1n14glq9cqg1716r7_at_4ax.com...
> On Thu, 21 Feb 2002 13:18:25 -0700, "Leigh Gold" <intan_5ee_at_yahoo.com>
> 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
> >
> >
> >
> Loosing sequence numbers is inevitable using the cache option (which
> is the default) I'm not sure this won't happen with the nocache
> option. However, I never use that as the sequence number is supposed
> to be meaningless, and your sequence number should be meaningful.
> Probably hiring a different developer will be cheaper in the long run.
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Sat Feb 23 2002 - 12:58:26 CST