We are in the process of switching from the C# Guid.NewGuid() random-ish guid generator to the sequential guid algorithm suggested in this post. While this seems to work well for MS SQL Server, I am unsure about the implications for Oracle databases, in which we store guids in a raw(16) field. Does anyone have any insight as to whether this algorithm would be good for creating sequential guids for Oracle as well as for MS SQL Server, or if a different variant should be used.
Thanks!
Using raw(16) seems to be a reasonable data type for GUIDs. The maximum size for the raw datatype is 2000 bytes and is supported in Oracle 9i, 10g and 11.
There is also a sql function for generating GUIDs, it's called SYS_GUID. see documentation here-> http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/functions153.htm
You may be interested in this article -> http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html
When an index block is 'too full' for one more entry, it is split.
Oracle has two paths, one optimized for 'sequential' style values and one for 'random' type values. If the new entry is going on the right-most end of the index you get a 90-10 split. If it is somewhere in the middle, you get a 50-50. If you want "new" values clustered together in the index then a sequential value is useful. If you want them scattered (eg to avoid contention on 'hot' blocks), then a random values is useful.
Whether the technique is 'good' for Oracle depends on what problem you are trying to resolve.
