Re: Re[2]: How do you genrate primary keys?

Hi!

> Well, if the commit is not too frequent, one natural way
> of generating primary key would be select max(last_change#) from
v$datafile;

No, you definitely don't want to do that!!!

v$datafile uses x$kccf% tables which cause several physical reads into controlfiles and x$kcvfh which shows datafile header information for every datafile in your database, causing one additional physical IO per datafile! None of those IOs are cached by Oracle. So, if you used the v$datafile approach, you'd be getting <number_of datafiles> + about 10 physical IOs for single PK value generation!

Yong already commented on the other issues with v$datafile usage.

Tanel.

> aware, this is the natural mechanism that ensures that any change is
properly
> enumerated and, thus, the best and most generic primary key. I understand
that
> someone might doubt this mechanism as I would never even dream of using
it,
> but SCN is the thing that comes naturally. Alternatively, one could
produce SCN
> from V$TRANSACTION (base + wrap).
>
>
> On 11/06/2003 12:54:38 PM, Cary Millsap wrote:
> > The implementations I've seen all did SELECT...FOR UPDATE.
> > Works.
> > Doesn't scale.
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - Performance Diagnosis 101: 11/19 Sydney
> > - SQL Optimization 101: 12/8-12 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> >
> >
> > -----Original Message-----
> > Jonathan Gennick
> > Sent: Thursday, November 06, 2003 7:59 AM
> > To: Multiple recipients of list ORACLE-L
> >
> > Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale
> > (hkchital_at_singnet.com.sg) wrote:
> > HKC> 1. Hit a table that keeps a counter.
> > HKC> Used to be a mechanism in the Oracle5 days [If I remember
> > correctly,
> > HKC> Sequences came in Oracle6]. Issues were with locking the single
> > HKC> record used as the generator or scanning for the max(value) of the
> > HKC> key.
> > HKC> Not quite sure I understand how you encountered concurrency issues,
> > though.
> >
> > My concurrency issues probably boil down to the locking
> > business. The app I'm thinking of originally did something
> > like:
> >
> > SELECT counter INTO :1
> > FROM counter_table
> > WHERE counter_name = 'table name';
> >
> > ...some app code goes here...
> >
> > UPDATE counter_table
> > SET counter := counter+1
> > WHERE counter_name = 'table name';
> >
> > Well, it all worked fine in single-user mode<grin>. But it
> > was easy enough for me to sit down in front of two
> > computers, create two new records, press SAVE at the same
> > time, and cause two sessions to grab the same key value,
> > because they would both issue the SELECT before either one
> > got around to the UPDATE. I couldn't screw things up
> > consistently, but just by hitting the SAVE button at the
> > same time I could screw things up often enough to make the
> > problem obvious.
> >
> > Maybe there's a way to lock the table, to make the above
> > approach work. In my case, I didn't bother trying to find
> > that solution. Once I did my little demo, it was easy enough
> > to convince the project manager that we should switch to
> > using Oracle sequences.
> >
> > Best regards,
> >
> > Jonathan Gennick --- Brighten the corner where you are
> > http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
> >
> > Join the Oracle-article list and receive one
> > article on Oracle technologies per month by
> > email. To join, visit
> > http://four.pairlist.net/mailman/listinfo/oracle-article,
> > or send email to Oracle-article-request_at_gennick.com and
> > include the word "subscribe" in either the subject or body.
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Gennick
> > INET: jonathan_at_gennick.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
> > also send the HELP command for other information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Cary Millsap
> > INET: cary.millsap_at_hotsos.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
> > also send the HELP command for other information (like subscribing).
> >
>
> Mladen Gogala
> Oracle DBA
>
>
>
> Note:
> This message is for the named person's use only. It may contain
confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
> Any views expressed in this message are those of the individual sender,
except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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
also send the HELP command for other information (like subscribing).