Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.