Re: Table Design!

On Wed, 16 Nov 2005 06:55:09 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

>gazzag wrote:
>>>>Except that a sequence, as demonstrated, actually won't on a
>>>>multi-user system.
>>
>>
>> Yes it will. That sequence will give you numbers in ascending order
>> which is what the OP asked for. Whether they're sequential or not is
>> irrelevant.
>
>A common belief but not necessarily true.
>
>A sequence, by default, caches 20 numbers in memory. The order in
>which those numbers are retrieved from memory is not guaranteed
>to be in ascending order ... or any order.
>
>It is true that during testing on stand-alone single-user instances
>they always are. But go to a multi-user system with high-activity
>and your mileage may vary.

Not necesary: ( Orcale 10G)

NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

Morgan, did you ever test (I did NOT)

0)
CREATE SEQUENCE TEST.RRR
START WITH 0
INCREMENT BY 1
MINVALUE 0
NOCACHE
NOCYCLE
NOORDER

1. select TEST.RRR%Nextval into X from dual
2. use 1 value from this statement ( I then get X = 1)
3. Pull out the plug: Oracle dies at once ( no power)
4. Put power back on
5. Issue 'select XXX_seq%Nextval from dual'
6. What do I get?? ( if I use default settings) X = 2 or X = 22 ?

Thank you

Best Regards

Rob Zijlstra Received on Sat Nov 19 2005 - 12:33:34 CST