Jonathan,
Here's the text of the article [I can't find it on the WebSite, it
is in the regular Oracle emails that I
receive from Builder.Com]
Understand SYS_GUID and sequences as primary
keys
Oracle8i introduced the concept of SYS_GUID, which had several advantages
over a conventional sequence that Oracle administrators may use. A
sequence generator simply creates a series of integer values from a given
starting point and increments that series automatically whenever it's
used in a select statement.
Sequence generator numbers are guaranteed to be unique only for a single
instance, which is unsuitable for use as a primary key in parallel or
remote environments, where a sequence in each environment might generate
the same number and result in conflicts. An identifier created by
SYS_GUID is guaranteed to be unique for each database.
Moreover, a sequence has to be part of a DML statement and, therefore,
requires a round-trip to the database (otherwise, it couldn't be sure
that its value was unique). A SYS_GUID is derived from timestamps and
machine identifiers that don't require trips to the database, saving
query overhead.
create table use_seq_table(id
integer);
create sequence use_seq_sequence;
insert into use_seq_table values
(use_seq_sequence_value.nextval);
REM - for some reason, the documentation uses raw(32)
create table use_guid_table(id raw(16));
insert into use_guid_table(sys_guid());
Many applications depend on sequence generators to create a
primary key for rows that don't have an obvious primary value, namely a
dataset where any of the columns could change once a record is created.
Thus, admins might be tempted to use SYS_GUID as a primary key on a table
instead of using sequence numbers. This works well in situations where
objects are generated in different databases on separate machines and
need to be merged back together later.
However, the value generated by SYS_GUID is a 16-byte raw value. The
integer generated by a sequence won't use 16 bytes until it gets to 10 to
the 30th power (two digits per byte), and only if the digits are fairly
unique:
SQL> select
dump(123456789012345678901234567890) from dual;
DUMP(123456789012345678901234567890)
--------------------------------------------------------------
Typ=2 Len=16: 207,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91
Shorter values mean less storage space for the table and the
index, as well as faster lookup access.
Using either SYS_GUID or a sequence will create performance overhead
somewhere in the database use cycle; it's just a question of where. For
SYS_GUID, the performance hit is during query time and creation time
(creating more blocks in the table and index to hold the data). For
sequences, the performance hit is during the query, when the SGA sequence
cache is used up. By default, a sequence caches 20 values at a time. If
the database is shut down without using those values, they will be lost.
Another obvious disadvantage to SYS_GUID-generated values is that it
becomes much more difficult to manage values, either typing them in or
populating them through scripts, or passing them as Web parameters.
For these reasons, SYS_GUID might not be such a good idea to use as a
primary key except in parallel environments or where it's desirable to
avoid managing sequence generators.
Scott Stephens worked for Oracle for more than 13 years in technical
support, e-commerce, marketing, and software development.
{Tim [Gorman], with your permission } :
These were Tim's comments when I forrwarded the article to him :
This article is incredible is so many ways!
First of all, the author asserts the fallacy that
SEQUENCE.NEXTVAL is not unique across clustered databases,
citing that sequences"simply creates a series of integer
values from a given starting point and increments that
series automatically", without giving thought as to how that
"automatic increment" is performed. I am pretty certain
that he is not confusing "distributed" with
"clustered", but
is in fact unclear on the basic concepts altogether, based
on the gaps in his "explanation".
Then, he cites that the SYS_GUID function is "derived from
timestamps and machine identifiers that don't require trips
to the database, saving query overhead", but he fails to
mention that in order to call the function, you have to be
connected to the database and issue a query or stored
procedure call. I wonder how much "savings" this entails...
:-)
Hemant
At 09:04 AM 03-11-03 -0800, you wrote:
Hello Hemant,
Monday, November 3, 2003, 11:29:26 AM, you wrote:
HKC> However, the Builder.Com article quite explicity asserts
HKC> "Sequence generator numbers are guaranteed to be unique only
for a single
HKC> instance, which is unsuitable for use as a primary key in
parallel or
HKC> remote environments, where a sequence in each environment might
generate
HKC> the same number and result in conflicts
Can you point us to the article? My guess is that the author
is not familiar with Oracle, and is basing the above
statement on his experience with some other database (DB2
perhaps?). There is no problem with using sequence numbers
in a RAC. No conflicts will occur. I've never heard of a
problem in that regard.
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@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@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@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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :
http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
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@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).
Received on Tue Nov 04 2003 - 08:19:26 CST
