Home » SQL & PL/SQL » SQL & PL/SQL » Sequential GUID's?? (Oracle 9i, Windows XP Professional)
Sequential GUID's?? [message #307965] Thu, 20 March 2008 10:51 Go to next message
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 #307967 is a reply to message #307965] Thu, 20 March 2008 11:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What DID you expect?
GUIDs are no more, no less then unique ids. (as far as I know)

[Updated on: Thu, 20 March 2008 11:04]

Report message to a moderator

Re: Sequential GUID's?? [message #307969 is a reply to message #307965] Thu, 20 March 2008 11:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Must have something to do with your DB configuration
SQL> 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 Go to previous message
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
Previous Topic: Add partition and Move Data
Next Topic: PLSQL - JAVA Integration
Goto Forum:
  


Current Time: Thu Dec 08 22:00:07 CST 2016

Total time taken to generate the page: 0.31380 seconds