| Sequential GUID's?? [message #307965] |
Thu, 20 March 2008 10:51  |
jnovak@lbschools.net
Messages: 3
Registered: March 2008
Location: Long Beach, CA
|
Junior Member |
|
|
Hello,
I am working with a RDB implemented in Oracle 9i. The primary keys in almost all tables are GUID's, implemented as RAW(16) columns in the data tables. While looking at the data in several of our tables I came across sequences of "GUID's" that were sequential, differing only in a couple of hexadecimal digits. An example of some of the values is provided below. Notice that the only variation is in digits 11 and 12.
.
.
.
43EB07F3D3E2532EE0409ACDCD94396D
43EB07F3D3E3532EE0409ACDCD94396D
43EB07F3D3E4532EE0409ACDCD94396D
43EB07F3D3E5532EE0409ACDCD94396D
.
.
.
This occurs in rows that were inserted all at the same time by one of our analysts. I pointed out that this type of behavior is counter to my understanding of how GUID's work, and that it appeared that someone had used a seed value and then incremented that value to obtain new "GUID's". I also pointed out that the correct approach would be to use SYS_GUID() to generate new GUID's. The response was that SYS_GUID() WAS used to generate the GUID's. Is this possible? Are there circumstances where SYS_GUID() will generate sequential sequences of GUID's?
Thank you,
John
|
|
|
|
|
|
| Re: Sequential GUID's?? [message #307969 is a reply to message #307965] |
Thu, 20 March 2008 11:08  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions168.htm#i79194
All I can see from reference manual this function sys_guid() will return a global unique identifier but within that session it will be sequential. Find my observation
Session 1 :
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
48E0847559C06264E044000000000000
SQL> /
SYS_GUID()
--------------------------------
48E0847559C16264E044000000000000
SQL> /
SYS_GUID()
--------------------------------
48E0847559C26264E044000000000000
Session 2 :
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Mar 20 16:04:38 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
48E0874283806322E044000000000000
SQL> /
SYS_GUID()
--------------------------------
48E0874283816322E044000000000000
Regards
Raj
|
|
|
|
| Re: Sequential GUID's?? [message #307971 is a reply to message #307969] |
Thu, 20 March 2008 11:18 |
jnovak@lbschools.net
Messages: 3
Registered: March 2008
Location: Long Beach, CA
|
Junior Member |
|
|
Raj,
Thanks for the reply. My puzzlement arises then because I can't reproduce the behavior you have described. Perhaps it is a function of the tool I am using to connect (Toad for Oracle by Quest Software). I issued the following command, constructed using the SQL Editor and executed in that environment:
select sys_guid() from dual union all
select sys_guid() from dual union all
select sys_guid() from dual;
The output from that command was:
FDD6AC634A8A413C992215A2F55114ED
6ACC2999E02240898E8542705089CF68
1DEEF88130294ECBB4CD8C1D84A5C79C
It seems inconceivable to me that each of the select statements would create a new session, but I guess it is possible.
John
|
|
|
|
| Re: Sequential GUID's?? [message #308014 is a reply to message #307971] |
Thu, 20 March 2008 15:25 |
scottwmackey
Messages: 515
Registered: March 2005
|
Senior Member |
|
|
Must have something to do with your DB configurationSQL> select sys_guid() from dual union all
2 select sys_guid() from dual union all
3 select sys_guid() from dual;
SYS_GUID()
--------------------------------
48E5289A0A4BE32FE040C80A4E0255A5
48E5289A0A4CE32FE040C80A4E0255A5
48E5289A0A4DE32FE040C80A4E0255A5
|
|
|
|
| Re: Sequential GUID's?? [message #308016 is a reply to message #308014] |
Thu, 20 March 2008 15:31 |
jnovak@lbschools.net
Messages: 3
Registered: March 2008
Location: Long Beach, CA
|
Junior Member |
|
|
Scott,
I think it is a platform issue. In the documentation that Raj sent to me the key phrase is "On most platforms...". It turns out that our production server is running on a Linux platform, while the development server that I have access to is running on a Windows Server 2003 platform. On that platform the SYS_GUID() function works like the NEWID() function in MSSQL.
Thanks for your response,
John
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
