Re: same sequence as primary key in more than one table

From: joel garry <joel-garry_at_home.com>
Date: Mon, 15 Jun 2009 09:37:18 -0700 (PDT)
Message-ID: <6a6dca13-1efa-4c5b-aa82-e4f864f9970d_at_n7g2000prc.googlegroups.com>

On Jun 15, 2:26 am, student4life <student4li..._at_gmail.com> wrote:
> Hello,
>
>  For example, I have 'sellers' and 'buyers' both need to authenticate
> from a database table 'users' with sequence as primary key which is
> also as primary key in tables 'sellers' and 'buyers' (both have one-to-
> one relationship with 'users') but I am not sure whether it's good
> practice to have sequence number jumping alternatively between tables.
> Could someone inform me what the best practice/design is under the
> scenario? TIA

You might have enqueue scaling issues if you share sequences between tables. How bad is app and version dependent, newer versions may not be so bad. You can even have problems with multiple instances on a sequence for logging in if the usage is high enough. See http://orainternals.wordpress.com/2009/03/10/systemstate-dump-analysis-nocache-on-high-intensive-sequences-in-rac/

http://www.freelists.org/post/oracle-l/Sequences-and-RAC-Inputs-Required,1

Of course, if you don't use RAC, you may not care, but it does tend to highlight where best practices are not followed. How volatile are sellers, buyers and users, anyways?

jg

--
_at_home.com is bogus.
AAA Dimaond Award  http://www3.signonsandiego.com/stories/2009/jun/14/1b14avia213050/?uniontrib

Received on Mon Jun 15 2009 - 11:37:18 CDT