Re: Sequence Caches
Date: 1996/07/11
Message-ID: <31E5D14D.19B4_at_earthlink.net>#1/1
I always use seq. to generate PK for master tables.
From other side, I can not imagine the case, when you would need them in
sequencial order. Futhermore:
When you delete record from table it will create the same "hole" in the
table's order. If even you do not delete records, "hole" also can
appear. This is logic for the last statement:
- Oracle "commits" sequences independently from master transaction.
- If your master transaction fails after seq.nextval (transaction is rolled back), your sequence number anyway has been increased ( current seq. number has been wasted). *) Works the same way for "before insert for each row" triggers.
Logic for "Suppose Oracle does not commit seq. independantly"
1) First and Second transactions use the same seq. 2) First transaction invoke seq.nextval before Second. 2) Second transaction commits before first. 3) Finaly, first transaction is rolled back. 4) Seq. number has been wasted after next has been writen into thedatabase. "Hole" exists.
*) Practicaly, triggers should work fine, but probability of "holes" !=0.
Am I right?
I would very appreciate if you explain it to me. Also, it would be great, if we can discuss some design issues, connected to system generated keys. If you are interested, let me know.
Eugene.
John Jones wrote:
>
> Sequences are chached in memory when the oracle instance is started.
> If you are using sequences for just random numbers this is ok. We
> used them for unique numbers and wanted them in order with out
> losing any numbers. Since they are cached in memory, any glich to
> memory causes numbers to be lost. Shutdowns, if it be normal or not
> will cause the numbers to be lost and a new set cached to memory
> when you restart the instance. If this is ok, then use it, if not
> create your sequences with the "NO CACHE" option.
>
> John Jones
> Oracle Consultant
> jmj22026_at_us0n05.glaxo.com
Received on Thu Jul 11 1996 - 00:00:00 CEST