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)
Received on Sat Jul 09 2005 - 00:40:07 CDT
