Re: Find the table's name that using sequences

In addition to what Jacques has mentioned, here is my 0.02.

Why only one sequence per table? Does it stem from the concern that a single sequence becomes "overloaded" with request to be inserted into multiple tables?

The overloading does not come from number of tables, but number of concurrent requests, which perhaps more indicated by the number of users in the system at any point in time, regardless of how many tables. If you have a single sequence serving PKs of 500 tables with an average of 1 concurrent user, is that worse than 500 concurrent users and 1 sequence per table? The load on the seqeunce will still be the same. So performance is not the right reason to look at this issue.

The correct reson is business. If you have 2 tables getting the their PK value from the same sequence, you will have "gaps" in the PK as each table will grab values from the sequence. Is that acceptable? If the answer is no, you shouldn't even consider sequences; they are _bound_ to have gaps. Using a independent sequence for a table sometimes makes sense to retrieve the last used number and guess the next PK value to be generated on that table. A sequence per table will allow that, multiple tables will not.

Another factor to use a single sequence for a table's PK is, as Rachel mentioned, sanity check. Our developers (encouraged_ by yours truly!) use a sequence per table and follow the naming convention as SEQ_<table_name>, just as an easy reference to the table. sometimes, it is required to use the same sequence number for two tables; the name then becomes SEQ_<table_name1>_<table_name2> and so on.

HTH. Arup Nanda

• Original Message ----- From: "Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, July 16, 2003 4:39 PM Subject: RE: Find the table's name that using sequences

> If you are using the sequence to generate the primary key for a table,
then the sequence should only be used for that table. I can't think of a pro to have one sequence shared for the primary keys on many different tables.
>
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> > Chris Grabowy
> >
> > Well, there could be business logic reasons as to why you
> > would have one
> > sequence per table.
> >
> > Also, I don't know if I would ever go with one sequence for
> > many tables,
> > sounds like a bottle neck to me. And how would one sequence
> > for many tables
> > impact scalability?? Or having lots of users hammering the
> > database?? And
> > what happens if you have to reset the sequence, then you have
> > to check the
> > primary key values on many tables. One sequence to one table
> > sounds good to
> > me, but I would love to hear pros/cons about this...
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jacques Kilchoer
> INET: Jacques.Kilchoer_at_quest.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jul 16 2003 - 15:27:31 CDT