Re: What's the most efficient primary key ?

Cross-posted to:

        comp.databases.informix
        comp.databases.ms-sqlserver
        comp.databases.oracle.server
        comp.databases.sybase

In article <9esplg$10246$1_at_ID-79573.news.dfncis.de>, Andrew Hamm <NOahammSPAN_at_sanderson.net.au> writes
>Joe Maloney wrote in message ...
>>The first two questions are why and where.
>>
>>That is, the most efficient key is not necessarily an identity value.
>>How many foreign key relationships (parent/child) are there going to
>>be? It may be more efficient to use a text string in some cases.
>>
>>Not all applications need primary keys.
>>This is the why.
>>
>>The most efficient key is probably going to be a hash value if it can
>>be reconciled to a given page/cluster/disk address. But that may take
>>petabytes of storage if you have billions of keys.
>>
>>This is the where.
>>
>>The whole thing hinges more on your application needs and platform
>>than an academic foot stomping.
>>
>To ward off any evil like cross-group flame-wars (and we need another one of
>those like we need a hole in the head), I'd like to say that OTC (the clown)
>was right insofar as the ifx server is concerned - pending use of an
>appropriate index to optimise access by the serial key. It's perfect for the
>requested job. Whether that applies to other engines, can only be answered
>by guru's of those other engines.

I suspect for any DB which has the equivalent of a SERIAL column type that it will be the most efficient, subject to the other provisos. It's physically small, can provide a *very* large number of keys and if necessary can be combined with other columns to fix their indexing problems if the distribution is very weird - something I came across on Ingres and then on Informix.
>
>It really sounds like "remco" is either fishing for an engine to choose, or
>he's trolling for a flame war. That's why I'm nervous...
>
>--
>"Dis act ain't about lafter - it's about comedy" - Andrew Dice Clay
>
>
>
 

-- 
Five Cats