Thanks for the question, Ralph.
Asked: August 02, 2012 - 3:42 am UTC
Last updated: August 02, 2012 - 9:31 am UTC
Version: 9 -> latest
Viewed 1000+ times
You Asked
Hi Tom,
I am currently thinking about the best way to store guids in the database for the primary key. The column type to be used for guids is as I read on your site raw(16).
I was wondering, if it is better to store random guids or sequential guids. This would have an impact on the index as far as I know. Having sequential guids would lead to a better clustering factor which maybe has also a better query performance. Referring to this article
http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database there is a small performance advantage storing sequential guids rather than random guids.
I also saw this thread
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:77564387253035 where you were discussing the differences between guids and sequences but I am not 100% sure at the moment what kind of guids to store. Hope you can help me there.
So my question here is, what is the best way to store guids in oracle regarding optimal indexes? Is there a difference inserting random or sequential guids? Is there a best way to generate sequential guids in order to provide the best format for oracle?
Thank you in advance for your answer.
Ralph
and Tom said...
Having sequential guids would lead to a better clustering factor which maybe has also a better query performance
it wouldn't have an effect. You'd be using 'where primary_key = ?', you'd be going for a single row - you would not be using 'where primary_key between ? and ?' or 'where primary_key > ?' - so it would be a single row lookup. The clustering factor would not be relevant.
the clustering factor only comes into play when the indexed column is going to be used to pick up hundreds of rows at a time, and this one wouldn't.
that article has this statement:
To get the best performance, most databases store rows in what's known as a clustered index, meaning that the rows in a table are actually stored on disk in a sorted order, usually based on a primary key value.
it should say "Microsoft and Sybase databases store rows in what's known as a clustered index"
It is false to say "most", as there is really one that does it mostly. You'd have to be using an index organized table in Oracle to have this be the case - that would actually be very rare.
So my question here is, what is the best way to store guids in oracle regarding optimal indexes?
there is only one - in a raw(16) (sure you could use a varchar2(32) but that would be a waste of space)
you don't need sequential guids.
Is this answer out of date? If it is, please let us know via a Comment