Re: What's the most efficient primary key ?

Consider if you really need the key. If you do, which it sounds like you do, in general a numeric index is most efficient, but if you are considering Oracle some variations may be worth considering.

1. Reverse indexing. In situations where the data is inserted in sequencial order (1,2,3,4...) the index is structured by reversing the number.

1234 then 1235, 1236 ....
would be changed to
4321 then 5321, 6321. This helps to balance the index rather than getting a lopsided btree index.

2) Character indexes can be efficient also since often large segments can be eliminated quickly. Just remember 00001 is different than 1.

See below also.

--
Robert Fazio
Senior Technical Analyst
dbabob_at_yahoo.com


> >Your index model is likely to be just as important, if not more so, than

 the


> >particular data type used for the key column(s).  Being able to isolate

 the


> >data reads to a particular section of disk, implying that a clustered

 index


> >or fragmented table is used, is likely to be of more benefit than

 worrying


> >about the number of CPU cycles spent comparing integers rather than

 strings.


> >

>   Integer           = 4 bytes.

>   String "12345678" = char(8) 8 bytes

>

>   1 million rows of integer  = 4 Mb to read from disk

>   1 million rows of char(8)  = 8Mb to read from disk

>

>   How can 8Mb be read nearly as fast as 4Mb? Double the disk I/O!


Depending on the system and how the data is layed out.  It is possible that
using 8 bytes could be more efficient.  If the system is reading chunks of
disk at a time (the norm), it may end up reading more for the 4 byte version
(more different blocks) than the 8 byte version.  Not always, but you have
to base your decision on the system, not just the size of the data.  There
is a reason why larger block sizes help in some tables, but hurt in others.

You need to consider the system, data, and access of that data to determine
the best course.   There is no hard and fast rule.



>

> >IMHO,

> >Chris

> >

> >

> >"worldonline" <remco.van.toor_at_iquality.nl> wrote in message

> >news:9elo7s$sf2$1_at_nereid.worldonline.nl...

> >> I want to create billions of unique key, wat's the best option?

> >>

> >> a compound key containing several integer-columns,

> >> a large datatype (decimal)

> >> or a string perhaps ??

> >>

> >> i would like the first options, it is good for using OID's

> >>

> >>

> >> thanks in advance,

> >> remco

> >>

> >>

> >>

> >

> >

>

> --

> David Williams