Re: HELP: How to generate sequential Primary Keys on a maste

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 7 Apr 2002 12:04:45 -0700
Message-ID: <a8q58d077a_at_drn.newsguy.com>


[Quoted] In article <1018179580.6010snx_at_aleytys.pc.my>, philip_at_aleytys.pc.my says...
>
>In article <a8nhvo013oh_at_drn.newsguy.com> tkyte_at_oracle.com writes:
>>In article <1018105530.6006snx_at_aleytys.pc.my>, philip_at_aleytys.pc.my says...
>
>>>>rubbish. Create sequence ... nocache ordered.
>
>>>So, um will a transaction rollback also rollback a sequence? There
>>>are certain occasions where your external auditor (unless it's AA)
>>>won't accept "That's the way sequences work" as a reason why there
>>>are missing numbers in your financial transactions.
>
>>and what happened in the "olden" days when people did it on paper and made a
>>mistake and crumpled up the invoice and thru it out (like I do with checks in>
>>my
>>checkbook).
>
>>Whatever the answer is -- do the same thing in your computer system -- create a
>>"voided" invoice or whatever you need later, after the fact.
>
>The external auditors don't like us DBAs by passing the business
>logic and updating the database directly. Heck even Oracle doesn't
>like us doing that to our Financials database.
>
>>I don't see what sequential buys you -- even for an auditor. Heck,
>>just issue "update invoice_table set invoice_number = rownum" when
>>they walk in the door if you like. Using something else as the
>>primary key of course (the invoice_id column is for the auditor who
>>doesn't have a clue about computer systems, the primary key is for
>>us). That would be easier then artificially trying to generate a
>>sequence number without gaps in a high end OLTP system.
>
>In Oracle Financials the primary key is always a sequence, but the
>user (auditor) visible document number is in certain cases generated
>from a lookup table which stores the last document number. For
>example AP.AP_CHECKS.CHECK_ID is a sequence, but
>AP.AP_CHECKS.CHECK_NUM is pulled from somewhere like
>AP.AP_CHECK_STOCKS.LAST_DOCUMENT_NUMBER .
>
>Perhaps you might like to explain to your colleagues in the
>Financials / Applications Group about the superiority of sequences
>under all circumstances.
>

[Quoted] [Quoted] they are doing it exactly the way I describe!

I said:

[Quoted] Heck, just issue "update invoice_table set invoice_number = rownum" when they walk in the door if you like. (thats the other sequential table you refer to)

[Quoted] Using something else as the primary key of course (the invoice_id column is for the auditor who doesn't have a clue about computer systems, the primary key is for us). (thats the SEQUENCE)

they are doing it that way already. The TRUE key of the table is not some gap free sequence number. We don't serialize on the generation of new rows, the "gap free sequence number" is an artificial thing not used by the system whatsoever (doesn't slow it down) and is there only to make people feel better.

>Philip
>
>---=====================================================================---
> Philip Chee: Tasek Corporation Berhad, P.O.Box 254, 30908 Ipoh, MALAYSIA
> e-mail: philip_at_aleytys.pc.my Voice:+60.5.291.1011 Fax:+60.5.291.9932
> Guard us from the she-wolf and the wolf, and guard us from the thief,
> oh Night, and so be good for us to pass.
>--
> þ 20483.15 þ Call me if you need my phone number!

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Sun Apr 07 2002 - 21:04:45 CEST

Original text of this message