tag:blogger.com,1999:blog-20160325.post114036101030090487..comments2022-12-11T06:49:09.876-08:00Comments on FeuerThoughts: Watch out for sequential Oracle GUIDs!Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-20160325.post-21784272326256070462014-08-07T10:50:43.581-07:002014-08-07T10:50:43.581-07:00I know I'm about 8 years too late, but it seem...I know I'm about 8 years too late, but it seems it continues to be an issue recently.<br /><br />Oracle Support Document 1371805.1 (DUPLICATE SYS_GUID generated on AIX can lead to ORA-1 errors during enqueue) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1371805.1Joel Garryhttps://www.blogger.com/profile/13325061229393838224noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-32695734921159549672011-10-07T09:06:47.483-07:002011-10-07T09:06:47.483-07:00Hi Steven. I'm a long-time reader; first-time...Hi Steven. I'm a long-time reader; first-time poster.<br /><br />Re "Watch out for sequential Oracle GUIDs!" from Feb-2006 ... <br /><br />I'm running Ora 10.2.0.4 on AIX. As in your example, I get the following sequential values for SYS_GUID:<br /><br />AEB87F28E222D08AE043803BD559D08A<br />AEB87F28E223D08AE043803BD559D08A<br />AEB87F28E224D08AE043803BD559D08A<br />AEB87F28E225D08AE043803BD559D08A<br />AEB87F28E226D08AE043803BD559D08A<br /><br />Doesn't Oracle see this as a deficiency in its implementation of GUIDs? I get that it's probably still unique... yet it's hardly random.Kaihttps://www.blogger.com/profile/05796112591925520083noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-74043413794292050752009-02-13T16:36:00.000-08:002009-02-13T16:36:00.000-08:00Piratenblog: Yes, at first I thought the numbers ...Piratenblog: Yes, at first I thought the numbers were all the same too. The counting is occurring in the middle of the hex number at digit 12. The remaining string is constant (for that DB session).EIAhttps://www.blogger.com/profile/02075578736980173924noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-85882096155693227072009-01-14T15:50:00.000-08:002009-01-14T15:50:00.000-08:00We are producing enterprise framework and three mo...We are producing enterprise framework and three months ago rewrite all database routines to work with GUIDs instead of auto-generated integers and other technologies. Before that we wasted two weeks, gathering all information available about guids as primary keys in databases.<BR/>It's quite a question, what is better, but GUID has several advantages, some can be found in previous posts and some others are (useful for several users working with db simultaneously):<BR/><BR/>1) Now primary keys can be generated on client-side without connection to database.<BR/> a. First it gives possibility to generate data offline (without connection to DB to get next autoincrement ID).<BR/> b. Several rows can be created with referenced columns:<BR/><BR/>Department Worker<BR/>---------- ---------------------<BR/>ID Name ID Department_ID Name<BR/><BR/>As you can generate both department and worker IDs in program, you can create for example, 2 departments and 7 workers with all fields fulfilled and just call 9 INSERT statements (or one packet query). In traditional (autoincremented) case first you need insert departments, get their ID's, fill Department_ID field for workers and insert them. In our case we add up to 100 rows from 8 tables connected by 18 referenced fields in one transaction without any crazy-mind logic.<BR/><BR/>2) Row ID is unique through database, not table. So one Guid is enough to define row (for example, we store ID's of recently deleted rows).<BR/><BR/>About problem of sequential and random Guids. Never, please never use random Guids as primary key for pretty large database. SELECT/UPDATE/DELETE will be OK, but INSERT will make DBMS to rebuild index greatly, so several inserts will slow down DBMS. Really, generating sequential Guids it's not a problem and solves this problem with INSERT.<BR/>But take care (as said in Chris F. comment), about bytes-order. If interested, I can share class (C#) that generates sequential ID's for different DBMS (MS SQL, MySQL, Vista DB, easily Oracle can be added) that generate pretty-random but strictly sequential IDs even from parallel computers.<BR/><BR/>Regards,<BR/>AdamAdamhttps://www.blogger.com/profile/01620984687206643596noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-72596189845293455842008-05-17T16:52:00.000-07:002008-05-17T16:52:00.000-07:00Thanks for the warning.A little point of view from...Thanks for the warning.<BR/><BR/>A little point of view from a project I used to work on; a person should not only consider the issue of distributed databases where you may have multiple instances of the same application/tables merging into other tables, but also the issue of distributed data itself. <BR/>Businesses are more and more using data mining and other business intelligence techniques to combine data from widely heterogeneous systems and simple sequential numbering schemes just won't work well in these combinations.<BR/><BR/>Several examples:<BR/><BR/>a) More and more, enterprise databases are cross referencing data from multiple applications and problem domains. The sales database references the inventory database, both of which may reference the customer database and/or the employee database. All of these databases may be part of different applications and indeed may use different DBMSs because the apps may have been provided by different vendors. Indeed, one app and/or database may not even expose the database - CRUD operations may take place via a SOAP or other web service interface.<BR/><BR/>b) MetaData - which is domain I used to work in - where the data types are defined so that you have a central repository to manage what the different data in different databases and apps map to in other databases and apps. Or you need a repository to store semantic information.<BR/><BR/>In my experience, we did not use the GUID functionality in various DBMSs for several reasons:<BR/><BR/>1) Not all DBMSs have this functionality and we needed to support at least three different DBMSs.<BR/><BR/>2) For various reasons we needed to generate the GUID before we interfaced with the database - we didn't want the GUID automatically generated and then have to retrieve it so we could use it elsewhere. This was especially true for bulk import operations. Then there is the issue of how the different DBMSs that support GUIDs implement them and how to use the results of one in another.<BR/><BR/>3) We had to interface with other apps which may have their own generated GUIDs.<BR/><BR/>That said, being a total beginner with regards to database design and performance, I had given no thought to the impact a GUID/UUID would have on the indexes (page locality). Doing a search with Google does turn up quite a number of performance problems when random GUIDs are used in databases as primary keys, so maybe this is why Oracle used sequential GUIDs.Developer Dudehttps://www.blogger.com/profile/01475883843077887466noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-23050070164541207052008-05-09T11:39:00.000-07:002008-05-09T11:39:00.000-07:00"See the difference? The algorithm that Oracle is ..."<I>See the difference? The algorithm that Oracle is using on these operating systems is clearly faulty; yes, the values are unique but they are far from even pseudo-random.</I>"<BR/><BR/>I wouldn't normally bother commenting on a two-year-old posting, but this is getting referenced and it is wrong. If you look at the spec for UUIDs -- RFC4122 -- you will see that there are more than one legitimate version of the UUID. Oracle, on Unix, is seems to be implementing Version 1 UUIDs. On Windows, it is probably calling Win32's UuidCreate(), which generates a Version 4 UUID (earlier versions of Windows also generated Version 1 UUIDs, but some people didn't like having their MAC address included in their UUIDs, so Microsoft switched to the slightly less reliable but more private Version 4 UUID).<BR/><BR/>UUIDs are required to be unique -- wildly different is not part of the specification.Unknownhttps://www.blogger.com/profile/12149502425108819261noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-48465046678611895582008-01-17T03:08:00.000-08:002008-01-17T03:08:00.000-08:00You can uses sys_guid() as a default column so tha...You can uses sys_guid() as a default column so that it is automatically populated.<BR/><BR/>CREATE TABLE guid_table (<BR/>pky RAW(16) default sys_guid() PRIMARY KEY<BR/>, NAME VARCHAR2(100));Paulo Rodrigueshttps://www.blogger.com/profile/00190647858460192668noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-45127968187722909602007-12-05T13:03:00.000-08:002007-12-05T13:03:00.000-08:00Stephen,f.y.i.If you reference the sys_guid functi...Stephen,<BR/><BR/>f.y.i.<BR/><BR/>If you reference the sys_guid function as sys_guid() in the insert statement for the column values it works. I just tried this and don't get the ORA-984Alleyhttps://www.blogger.com/profile/09646137380064142910noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-90283376544885698422007-11-09T10:03:00.000-08:002007-11-09T10:03:00.000-08:00(late to the game, found this via google)SYS_GUID ...(late to the game, found this via google)<BR/><BR/>SYS_GUID is sequential because random GUIDs play hell with indexes.<BR/><BR/>SQL Server has a sequential GUID generator as well for the same reason.<BR/><BR/>I don't see the need for a random GUID. Unique is unique. If you're relying on the randomness to make your IDs "unguessable", you have a flawed approach to security.Unknownhttps://www.blogger.com/profile/07691598392993513921noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-13289623218041802192007-05-21T13:54:00.000-07:002007-05-21T13:54:00.000-07:00The error with the statementINSERT INTO guid_table...The error with the statement<BR/><BR/>INSERT INTO guid_table<BR/>VALUES (SYS_GUID, 'Steven');<BR/><BR/>can be solved by typing in the function with the parenthesis thus:<BR/><BR/>INSERT INTO guid_table<BR/>VALUES (SYS_GUID(), 'Steven');<BR/><BR/>That works!DrLuvhttps://www.blogger.com/profile/15610883559900734275noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-58894728556647162312007-03-28T05:44:00.000-07:002007-03-28T05:44:00.000-07:00Hi, i just found you blog after searching for guid...Hi, i just found you blog after searching for guid generation on oracle db.<BR/><BR/>first thing i did, i tested that little script and ... well, it did give me 5 times the same string :(<BR/><BR/>but better this way than searching forever and wondering why nothing works like intended. thanks.<BR/><BR/>http://piratenblog.wordpress.com/Piratenbloghttps://www.blogger.com/profile/11097042717826375762noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-32351906171030903052007-03-13T12:43:00.000-07:002007-03-13T12:43:00.000-07:00There is one major flaw in your formatting algorit...There is one major flaw in your formatting algorithm for Guids. It only affects you if your data needs to be cross-platform with SQL Server. To see what I mean, take any guid value and do this on SQL Server:<BR/><BR/>select convert(binary(16),'your guid here')<BR/><BR/>Notice how the byte ordering of the binary(16) is quite different from the apparent ordering of the bytes in the GUID string value. <BR/><BR/>Suppose I have a table on Oracle and SQL Server. <BR/><BR/>I populate this table with:<BR/><BR/>On SQL Server<BR/><BR/>insert into sometable (pkcol, col2)<BR/>values ('2ebe23c4-d19a-11db-8314-0800200c9a66','foo');<BR/><BR/>and on Oracle, if I simply take out the dashes and do:<BR/><BR/>insert into sometable (pkcol, col2)<BR/>values (hextoraw('2ebe23c4d19a11db83140800200c9a66'),'foo');<BR/><BR/><BR/>Now, I right a .Net application which fetches the first row of sometable from both platforms.<BR/><BR/>The System.Guid returned from SQL Server will not be equal to the System.Guid returned from Oracle.<BR/><BR/>Why? Because on SQL Server, the base datatype of a Guid is actually a binary(16) (equivilent to Raw(16) on Oracle). But when the Guid value is marshalled into the binary, the bytes are re-ordered like this:<BR/><BR/>432165769ABCDEF<BR/><BR/>Yuck.<BR/><BR/>So, if you're going to be a multi-platform product, either:<BR/><BR/>a) use the Binary(16) datatype on SQL Server to hold your guids or<BR/>b) write an icky byte-swapping routine on Oracle and make your application slow as molasses.Chris Fhttps://www.blogger.com/profile/04016023945658273948noreply@blogger.comtag:blogger.com,1999:blog-20160325.post-65437216882521547542007-03-13T09:29:00.000-07:002007-03-13T09:29:00.000-07:00The error you were getting with:INSERT INTO guid_t...The error you were getting with:<BR/><BR/>INSERT INTO guid_table<BR/>VALUES (SYS_GUID, 'Steven');<BR/><BR/>(Awwww, no 'tt' tags allowed?)<BR/><BR/>I ran into that myself; it just seems to be a pecularity of using a function in the VALUES area, but it seems to work just fine if you specify the columns first.<BR/><BR/>INSERT INTO guid_table (pky, NAME)<BR/>VALUES (SYS_GUID, 'Steven');<BR/><BR/>I was doing tests with GUIDs on Oracle to see whether it can have a NULL in that column, and indeed, and fortunately, it can.Ritchie Annandhttps://www.blogger.com/profile/16095044509186974971noreply@blogger.com