Sequential (comb) GUIDs for Oracle

views:

256

answers:

Sequential (comb) GUIDs for Oracle

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

Oliver Michels 2010-04-07 11:51:19

Thank you for your answer. However, what I'm after is not whether raw is a good data type for guids in Oracle, but rather if the algorithm in question will cause needless index fragmentation in Oracle, even though it seems to be a good choice for MS SQL Server. Furthermore, I need to generate my guids on the client, so the SYS_GUID function will be of little help.

Eyvind 2010-04-07 12:36:35

What exactly is needless index fragmentation? Of course the GUIDs will be spread (fragmented) thru the entire room of possible data. Thats what GUID is for. The fragmentation of the data is an inherent property of the GUID algorithm, which the database has to handle, no matter if it's sql-server or oracle. The index implementation can handle this, see -> http://en.wikipedia.org/wiki/B-tree

Oliver Michels 2010-04-07 12:47:11

The idea is to use an algorithm that generates *sequential* guids, instead of the standard randomized version.

Eyvind 2010-04-07 13:00:52

Oliver Michels 2010-04-07 13:20:17

Yes, I am referring to COMB GUIDs (as the title states). Specifically, I am referring to the NHibernate implementation of sequential (comb) GUIDs (also linked in the post). As you can see from that algorithm, the bits are ordered specifically for the sorting algorithm applied by MS SQL Server for the UNIQUEIDENTIFIER data type. Thus, to restate my question (hopefully clearer this time): How will such data be sorted in a raw(16) field in Oracle? Will the values still be in "random" order due to the bits from the .NET Guid.NewGuid() algorithm, or will they be ordered sequentially?

Eyvind 2010-04-09 08:19:44

So, perhaps you are referring to this discussion from the NHibernate community -> http://nhforge.org/blogs/nhibernate/archive/2009/03/20/nhibernate-poid-generators-revealed.aspxNHibernate has invented the Guid.Comb algorithm, which cures the problem of table fragmentation in ms-sqlserver. Due to the fact that oracles works in a complete different way as my-sqlserver, when it comes to managing database diskblocks, i consider the Guid.Comb algorithm as useless but not harmfull, when used as an identity generator for oracle.

Oliver Michels 2010-04-09 10:15:09

oracles works in a complete different way as ms-sqlserver, when inserting new rows in database blocks (PCTFREE, PCTUSED, extend sizes, locally managed tablespaces, etc., etc., etc.) Therefore the requirements for avoiding "table" fragmentation are complete different.

Oliver Michels 2010-04-09 10:22:57

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.

Gary 2010-04-08 00:30:03

ansaurus

tags:

views:

answers:

Sequential (comb) GUIDs for Oracle

related questions