Re: unsequential sequences

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1120888873.872191_at_yasure...
> Ed Prochak wrote:
>
>> Sybrand Bakker wrote:
>>
>>> On 8 Jul 2005 13:23:06 -0700, "Kevin Blount" <kevin_at_questionmark.com>
>>> wrote:
>>>
>>>
>>>> This may have aked before, but my searching for such threads didn't get
>>>> the help I need, so here we go (again):
>>>>
>>>> I have a sequence and trigger that allow me to automatically assign an
>>>> "ID" to a table record. The sequence has the following properties (let
>>>> me know if I missed anything out)
>>>>
>>>> minimum = 1
>>>> maximum = 1.0E28
>>>> interval = 1
>>>> last number = 281
>>>>
>>>> cycle values = unselected
>>>> order values = unselected
>>>> Cache = default
>>>>
>>>> the problem is that the IDs created tend to a) end in 1 and b) jump 20
>>>> numbers.
>>>>
>>>> if I were to insert a new record on two different days, the changes are
>>>> that the IDs would be 301 and 321 (based on the last number above).
>>>> However, if I were to add them both at the same time, or within a small
>>>> time period) they would probably be 301 and 302.
>>>>
>>>> My DB admin has spotted the issue, but doesn't know the cause or the
>>>> fix.
>>>>
>>>> Can anyone help? I doesn't really effect any of the scripts I'm
>>>> writing, but when those IDs are visible it make my applications look
>>>> wierd, when 90% of the assigned IDs end in 1, ya dig?
>>>>
>>>> thanks for reading.. many thanks for replying :)
>>>
>>>
>>>
>>> Sequences are primarily intended for surrogate keys. Which means the
>>> number really doesn't have any meaning at all.
>>> That said, sequences are not guaranteed to be assigned without holes.
>>> Ie, if you select a sequence value, but roll back the transaction, the
>>> sequence isn't lowered, so you 'loose' a sequence number.
>>> This is of course documented.
>>> If you want sequences to be ordered, you need to define them ordered.
>>> The default is noorder, as documented.
>>> If you want to 'loose' less sequences numbers, you need to make sure
>>> you define them nocache. By default 20 values are cached, as
>>> documented. You can define them with nocache, evidently with some
>>> performance penalty associated (because Oracle would have to read and
>>> update it's internal sequence table every time).
>>>
>>> In short, if you don't want 'holes', don't use sequences.
>>> And please read the documentation before posting.
>>>
>>>
>>> --
>>> Sybrand Bakker, Senior Oracle DBA
>>
>>
>> The fact that his DBA doesn't know all this is what struck me as a
>> concern.
>
> Unfortunately the sole requirement for being hired as a DBA is that you
> convinced whoever interviewed you of your competence. Actual competence
> is not required.
>
> A friend of mine that interviewed with one of the largest banks in the
> US found this out when the VP interviewing him said: "If I knew what
> questions to ask you ... I wouldn't need you."
>
> For the following 3 years he worked at the bank amazed that they could
> successfully unlock the doors in the morning.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

unfortunately for many managers, hiring a DBA is like hiring a surgeon, auto mechanic, or plumber...

++mcs Received on Sun Jul 10 2005 - 05:56:46 CDT