Storing GUIDs in Oracle
Jeff Boenig
Oracle 9i and above. I want to use GUIDs as the primary key in several
tables. MS SQL supports this nicely using the UNIQUEIDENTIFIER data type.
The ADO.NET OleDb data provider automatically converts it to a System.Guid.
Is it possible to store GUIDs in Oracle databases? There doesn't appear to
be a GUID data type in Oracle 9i. Could I store it as RAW(16)? If I do,
would I be able to create an index on that column? Does Oracle 10i have
support for GUID?
Thanks.
Jeff Boenig
Elsinore Technologies, Inc.
http://www.elsitech.com
Noons
news:3f1e9642$1...@btitelecom.net...
>
> Is it possible to store GUIDs in Oracle databases? There doesn't appear to
> be a GUID data type in Oracle 9i. Could I store it as RAW(16)? If I do,
> would I be able to create an index on that column? Does Oracle 10i have
> support for GUID?
You know, from time to time it's probably worth
reading a manual or two about Oracle...
in SQL Reference, function SYS_GUID, you'll find:
<quote>
SYS_GUID generates and returns a globally unique identifier (RAW value) made up
of 16 bytes. On most platforms, the generated identifier consists of a host
identifier and a process or thread identifier of the process or thread invoking
the function, and a nonrepeating value (sequence of bytes) for that process or
thread.
Examples
The following example adds a column to the sample table hr.locations, inserts
unique identifiers into each row, and returns the 32-character hexadecimal
representation of the 16-byte RAW value of the global unique identifier:
ALTER TABLE locations ADD (uid_col RAW(32));
UPDATE locations SET uid_col = SYS_GUID();
SELECT location_id, uid_col FROM locations;
LOCATION_ID UID_COL
----------- ----------------------------------------
1000 7CD5B7769DF75CEFE034080020825436
1100 7CD5B7769DF85CEFE034080020825436
1200 7CD5B7769DF95CEFE034080020825436
1300 7CD5B7769DFA5CEFE034080020825436
.
</quote>
Is this enough?
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
Maximus
> Oracle 9i and above. I want to use GUIDs as the primary key in several
> tables. MS SQL supports this nicely using the UNIQUEIDENTIFIER data type.
> The ADO.NET OleDb data provider automatically converts it to a
System.Guid.
>
> Is it possible to store GUIDs in Oracle databases? There doesn't appear
to
> be a GUID data type in Oracle 9i. Could I store it as RAW(16)? If I do,
> would I be able to create an index on that column? Does Oracle 10i have
> support for GUID?
Load the Oracle SQL reference into Adobe Acrobat, search for "GUID."
Why are so many programmers completely useless at doing basic research?
Yes I'm flaming you.
Jeff Boenig
(aside from alot of hits on the word Guide) is the documentation for the
SYS_GUID() function. That answers the question of whether a GUID can be
stored in the database. The other part of my question is whether you can use
it as a primary key. The documentation talks about RAW and LOB datatypes
together and states that LOB columns cannot be used as primary keys and
cannot be indexed. It doesn't say whether the same applies to RAW columns.
I was also hoping to find out if anyone has tried this with the ADO.NET to
see if it converts the column properly. I was also hoping to get some
feedback from people who have used GUIDs as primary keys in Oracle
databases, to find out what they thought of the technique.
There isn't a straightforward answer to my questions in the SQL Reference. I
want to design my database to work equally well with MS-SQL and Oracle,
which is why I thought posting these questions on this newsgroup would be a
constructive thing to do.
The purpose of a newsgroup is exchange ideas and experience. That's what I
was looking for and I think that my questions are relevant.
If you didn't think my question was worthy of answer, you shouldn't have
posted a response. I think you took the trouble to respond because you get
some sort of weird satisfaction out of telling someone to RTFM. No, I don't
know as much about Oracle as you do. You're soooo smart. Maybe the hostile
response had something to do with my mention of MS-SQL.
"Maximus" <qwe...@qwqwewq.com> wrote in message
news:bzxTa.523341$Vi5.13...@news1.calgary.shaw.ca...
Jeff Boenig
leads me to believe that GUIDs are not supported.
http://www.datadirect-technologies.com/download/docs/dotnet/dotnetref/dotnetOra.html
Table 5.4 in that document shows mappings of .NET data types to OracleDB
types. The entry for GUID says "not supported".
"Jeff Boenig" <jeff....@elsitech.com> wrote in message
news:3f1e9642$1...@btitelecom.net...
Maximus
Logially, one would begin by examining the SQL reference to discover that
the sys_guid() function returns a 16 byte raw (binary) value. Examining the
SQL reference again, one discovers that a datatype of RAW(16) is sufficient
to store the binary value returned by sys_guid(). Examining the SQL
reference once again, one discovers that one can indeed create indexes and
primary keys on raw (binary) datatypes. Yes, RTFM!
Maximus
> I found some documentation on the ADO.NET Oracle data provider that also
> leads me to believe that GUIDs are not supported.
>
>
http://www.datadirect-technologies.com/download/docs/dotnet/dotnetref/dotnetOra.html
>
> Table 5.4 in that document shows mappings of .NET data types to OracleDB
> types. The entry for GUID says "not supported".
It might help if you learn what a GUID is first, then you'll have a clear
answer in the palm of your hands.
Sybrand Bakker
<jeff....@elsitech.com> wrote:
>There isn't a straightforward answer to my questions in the SQL Reference. I
>want to design my database to work equally well with MS-SQL and Oracle,
>which is why I thought posting these questions on this newsgroup would be a
>constructive thing to do.
As MS-SQL and Oracle are two completely different architectures, and
apart from PL/SQL Oracle treats the RAW(16) as a VARCHAR2, requiring
implicit conversion all the time,. you are going to end up in hell.
Please reconsider.
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Maximus
news:2kqthvcvga18jsbcc...@4ax.com...
> On Wed, 23 Jul 2003 14:21:28 -0400, "Jeff Boenig"
> <jeff....@elsitech.com> wrote:
>
> >There isn't a straightforward answer to my questions in the SQL
Reference. I
> >want to design my database to work equally well with MS-SQL and Oracle,
> >which is why I thought posting these questions on this newsgroup would be
a
> >constructive thing to do.
>
> As MS-SQL and Oracle are two completely different architectures, and
> apart from PL/SQL Oracle treats the RAW(16) as a VARCHAR2, requiring
> implicit conversion all the time,. you are going to end up in hell.
> Please reconsider.
UUIDs can also be stored in ascii hexdecimal format, this gets around
implicit conversions but of course requires more storage.
Jeff Boenig
So are you're saying that using a RAW(16) column as a primary key would
adversely affect performance? Would it cause other problems too?
Thanks!
"Sybrand Bakker" <gooid...@sybrandb.demon.nl> wrote in message
news:2kqthvcvga18jsbcc...@4ax.com...
Jeff Boenig
I understand what a GUID is. That does nothing to explain why the Oracle
ADO.NET data provider doesn't support them.
"Maximus" <qwe...@qwqwewq.com> wrote in message
news:BaBTa.496396$ro6.11...@news2.calgary.shaw.ca...
Jeff Boenig
instead of the flame war. I did consider storing it as a fixed CHAR(32), but
I don't like the idea of my keys being that large for performance reasons.
Any guess as to which might perform better - CHAR(32) or RAW(16)?
I've also considered using an 64 bit integer for my primary keys instead. I
lose global uniqueness, but I would gain a more efficient index. Anyone know
of any caveats to using 64-bit integers as primary keys?
Thanks.
"Maximus" <qwe...@qwqwewq.com> wrote in message
news:1oCTa.524755$Vi5.13...@news1.calgary.shaw.ca...
Sybrand Bakker
<jeff....@elsitech.com> wrote:
>Thank you very much. You're feedback is very constructive.
>
>So are you're saying that using a RAW(16) column as a primary key would
>adversely affect performance? Would it cause other problems too?
>
>Thanks!
>
As long as you primarily take into account, you will only have native
RAWs when using pl/sql and *hence* you the appropiate conversion
function (HEXTORAW) when your variable is a varchar2, nothing should
go wrong.
(So you are using <primary key column> = HEXTORAW(<your varchar2)
However, when you have end-users or other applications firing ad-hoc
queries you may well run into implicit conversion. The implicit
conversion always comes down to
RAWTOHEX(<primary key column) = <your varchar2 or literal>
which you will dump you in a full table scan.
One of the consequences also is: when you run tkprof on the app,
tkprof will come up with incorrect explain plans,as tkprof treats your
literal or your bind variable as a varchar2. tkprof (and other tools)
don't know what RAWs are.
One other problem I have in a specific app is : all primary keys are
RAWs. The RAWs do not contain any meaningful info. I would like to
partition those tables, as they are *huge*, but I can't do anything,
because the range of my RAWs.
IMO: this means as far as I am concerned RAW(16) is a big pain in the
butt and should be avoided at all costs.
Sybrand Bakker
<jeff....@elsitech.com> wrote:
>I've also considered using an 64 bit integer for my primary keys instead. I
>lose global uniqueness, but I would gain a more efficient index. Anyone know
>of any caveats to using 64-bit integers as primary keys?
Oracle doesn't have native 64-bit integers. Numbers are basically
stored as packed decimals. However number columns don't have the
problems asssociated with RAWs
Maximus
> Now you're just name calling. Grow up.
What name did I call you?
> I understand what a GUID is. That does nothing to explain why the Oracle
> ADO.NET data provider doesn't support them.
GUIDs can be stored as binary or character data using exisiting datatypes in
Oracle, a special datatype is not required. Since Oracle does not have a
special built-in datatype for GUIDs, this would explain why Oracle's ADO.NET
doesn't support them.
You haven't provided any details as to what purpose you are using GUIDs.
The theory behind GUIDs is in distributed computing where you need to
identify data from completely independant sources with a high degree of
certainty that no two items will have conflicting identifiers. Unless you
are developing such an application you don't need them.
Jim Kennedy
(eg use a sequence and then at most the key will take up 22 bytes and most
of the time much less than that) Why people want to use them for
meaningless primary keys is a mystery to me, but yes it is possible.
Jim
--
Replace part of the email address: kennedy-down_...@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Jeff Boenig" <jeff....@elsitech.com> wrote in message
news:3f1ed48a$1...@btitelecom.net...
Billy Verreynne
> Load the Oracle SQL reference into Adobe Acrobat, search for "GUID."
>
> Why are so many programmers completely useless at doing basic research?
>
> Yes I'm flaming you.
You call that a flame..? A rather pitiful attempt. I'm not smelling
any scorched butt hair. Heck, you did not even used RTFM! :-)
--
Billy
Sybrand Bakker
wrote:
Please ask Nelson Mandela for a bigger supply of coffee. You really
need it
Billy Verreynne
> Please ask Nelson Mandela for a bigger supply of coffee. You really
> need it
Cannot. He's no longer ruining.. er running.. the country. Mbeki is -
and doing a darn fine job of it too.
Come on Sy. Heck, even you do a better job at flamimg than that
pitiful RTFM attempt. We have standards in this newsgroup - I was
simply pointing that out.
--
Billy
S. Ek hoop jy het a sin vir humor Sybrand en dis nie heeltemal
verdroog nie. ;-)
Daniel Morgan
I can't speak for Maximus but will give you the following for your perspective.
1. No one here has anything against SQL Server, Microsoft, Bill Gates, or the
city of Redmond Washington. Many of us work in multiple database products
including those from Microsoft.
2. It is a well recognized fact that the vast majority of people that come here
from a SQL Server background seemingly are incapable of using a search engine or
opening a manual.
3. Had you gone to http://tahiti.oracle.com you would have found more than
sufficient information on GUIDs. Same goes with http://otn.oracle.com,
http://www.google.com, and numerous other sites. A search at tahiti returned
4,897 matches to a search on "GUID".
So drop the attitude ... we aren't biggots ... and understand that to work with
Oracle REQUIRES reading and research. And that to get along in this group
requires demonstrating that you have made an effort to help yourself.
Regards,
--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)