tag:blogger.com,1999:blog-7395977411859619892.post8915434480494173439..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: SYS_GUID()Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-7395977411859619892.post-78611102063897549792014-02-18T10:33:18.124+01:002014-02-18T10:33:18.124+01:00Like Jeffrey mentionend, if guessing ID's brea...Like Jeffrey mentionend, if guessing ID's breaks your system, you should have a look for another job. Basically i had to change for several years from Oracle to SQL Server, where this "crazy idea" of GUIDs is omnipresent. in short:<br />Tons of waste data to represent easy, small and basically useless information. as a PK is just an arbitrary value, you do not have to order it or Anonymoushttps://www.blogger.com/profile/16958429090654655075noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-79229826673741996092012-11-09T09:21:06.181+01:002012-11-09T09:21:06.181+01:00If your app allows access to any row given an arbi...If your app allows access to any row given an arbitrary key value, it's already insecure, regardless of whether you use a sequence or not.Jeffrey Kemphttps://www.blogger.com/profile/04255101699328756710noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-82384831054956739512012-11-07T19:29:43.091+01:002012-11-07T19:29:43.091+01:00Another con to sequences in a web environment is s...Another con to sequences in a web environment is security. Sequence IDs can be easily guessed and allow access to data in nearby rows in the database.Seanhttps://www.blogger.com/profile/08669804294712987184noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-70755846836567513072012-06-13T22:58:38.006+02:002012-06-13T22:58:38.006+02:00FYI, I just had opposite results testing 1 million...FYI, I just had opposite results testing 1 million inserts with RAW(16) GUID vs NUMBER(16) sequence-based key.<br /><br />About 13 seconds (on multiple runs) for GUID.<br />About 30 seconds (on multiple runs) for Sequence.<br /><br />Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0<br /><br />drop table mat;<br />create table mat (<br /> mat_id raw(16) default sys_guid() Nihimonhttps://www.blogger.com/profile/17081176857875210320noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-49629215530372511902010-01-29T11:37:27.860+01:002010-01-29T11:37:27.860+01:00I should add, it isn't monotonic either; next ...I should add, it isn't monotonic either; next two selects are:<br /><br />SQL> /<br /><br />SYS_GUID()<br />--------------------------------<br />A72FA8729A38444B82AEDEB875E6DF47<br /><br />SQL> /<br /><br />SYS_GUID()<br />--------------------------------<br />886B805C723340F3BD37F2C2A322ECC3<br /><br />SQL><br /><br />RegardsNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-43111405206255925922010-01-29T11:35:18.469+01:002010-01-29T11:35:18.469+01:00@Rohan: it's not sequential, it's hust a s...@Rohan: it's not sequential, it's hust a string:<br /><br />SQL> select sys_guid() from dual;<br /><br />SYS_GUID()<br />--------------------------------<br />9B1085FF611A4518A3211A495E36B703<br /><br />SQL> /<br /><br />SYS_GUID()<br />--------------------------------<br />9BAE7CE712D24D90A289861086B450C7<br /><br />SQL><br /><br />HTH, NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-80607277781924152902010-01-29T11:31:40.210+01:002010-01-29T11:31:40.210+01:00Is the SYS_GUID is incremental. I.e. can it's ...Is the SYS_GUID is incremental. I.e. can it's output be in sequencial order? or its just a unique string?Rohan Deshmukhhttps://www.blogger.com/profile/02202706879675781460noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-20087665088797695452009-12-23T14:48:34.280+01:002009-12-23T14:48:34.280+01:00Thanks Connor.
I just looked up some information ...Thanks Connor.<br /><br />I just looked up some information and found this article about sequences and RAC: http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-182881363421635982009-12-23T13:32:31.303+01:002009-12-23T13:32:31.303+01:00You *can* guarantee sequence order in a RAC system...You *can* guarantee sequence order in a RAC system (but you do incur some cross-instance traffic to do so).<br /><br />Cheers<br />ConnorConnor McDonaldhttps://www.blogger.com/profile/06246356571229889735noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-69525673245181788302009-12-09T21:33:42.346+01:002009-12-09T21:33:42.346+01:00Excellent comments, all. Thanks for the extra (dis...Excellent comments, all. Thanks for the extra (dis)advantages, twists (I forgot about RAC!), thoughts and the links!<br /><br />Regards,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-56419844311561667472009-12-09T13:20:35.245+01:002009-12-09T13:20:35.245+01:00Hi Rob,
Interesting post! There also must be a re...Hi Rob,<br /><br />Interesting post! There also must be a reasons why GUIDs are used in for example BPEL and ESB schemas.<br />A quick performance comparison can be found <a href="http://preferisco.blogspot.com/2007/02/unique-ids-for-multi-master-replication.html" rel="nofollow">here</a>.<br /><a href="http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html" rel="Anonymoushttps://www.blogger.com/profile/00675618539219778792noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-79248176135457638472009-12-09T13:03:53.210+01:002009-12-09T13:03:53.210+01:00I can vouch for comparative slowness of guid over ...I can vouch for comparative slowness of guid over sequences; see <a href="http://preferisco.blogspot.com/2007/02/unique-ids-for-multi-master-replication.html" rel="nofollow">Unique IDs for Multi-master Replication</a>.<br /><br />Regards NigelNigelhttps://www.blogger.com/profile/10826219818302312878noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-53033947397190383992009-12-09T07:54:39.351+01:002009-12-09T07:54:39.351+01:00Hi.
I've used SYS_GUID in a project and it wo...Hi.<br /><br />I've used SYS_GUID in a project and it worked fine. Actually, the project in question used a combination of SYG_GUID and a Java stored procedure to generate UUIDs.<br /><br />http://www.oracle-base.com/articles/9i/UUID9i.php<br /><br />I wouldn't say either method (sequences or GUIDs) are a clear winner. As always, both methods have their pros and cons.<br /><br />If you Tim...https://www.blogger.com/profile/17721555946005999179noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-19985686333014842552009-12-09T04:12:45.325+01:002009-12-09T04:12:45.325+01:00I've had a few people trying to convince me to...I've had a few people trying to convince me to use SYS_GUIDs, I'm just not buying it yet. I'm not convinced as to any real or perceived advantage.<br /><br />"You can no longer see the order of inserts by the ID column."<br />You probably know this, but I thought it worth mentioning, on a RAC system you can't count on the sequence numbers to be in order. That quickly oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-46378908653263909352009-12-09T00:57:09.273+01:002009-12-09T00:57:09.273+01:00Indexes ! Sequence generated values will always in...Indexes ! Sequence generated values will always increment and be added at the high end of the index.<br />SYS_GUID values will be all over the place. On the plus side, that may reduce contention for hot blocks , but on the minus side your index inserts will be all over the place. All the arguments for/against reverse key indexes would apply.SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.com