Re: Best data type for indexing??

In article <2kh5j7$4t0_at_sugar.NeoSoft.COM>, chadb_at_sugar.NeoSoft.COM (Chad A Brockman) writes:
> Iv'e been searching through my Oracle manuals for hours and I can't come
> up with a good answer. What is the best (i.e. fastest) data type to use
> for an index, if it can be any type. I would think a raw the size of a
> word on the target machine, but it's not portable. So I'm assuming a
> number would be the next best. Is this a valid assumption. On a side

A raw would be the best for storing a binary number in it's shortest form but then you have to ensure that you always pack the number in appropriately for the platform you're running on.

Hmm ... I suppose you could compress character strings and store them in RAW fields as indexes as well.

It does mean you have to manage all these packing and unpacking routines in your application though.

Next best would be NUMBER fields which are stored in a pseudo binary coded decimal. Perhaps character fields could be compressed and stored here as well.

Using a compression algorithm would NOT be an answer as the value of the key would change and the order of retrieval would be randomised.

Oracle does compact their indexes by removing common leading bytes so I don't know if your changes would save much space.  

> note, if no precision is specified on a number, does Oracle store the
> maximum number of bytes for that number? (i.e. if the number is 1 does it
> take up a byte for the mantissa and a byte for the number, or does it
> take a byte for the mantissa and however many bytes it takes to store 38
> digits)

No Oracle NUMBERs are variable length. They use only what they need. The manual describes the storage mechanism quite well.

>
> Thanx,
> Chad
> --
> (-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-)
> ) Chad A.Brockman - (
> ( VertiComp, Inc. - I'm just an OS/2 junkie!! )
> ) chadb_at_sugar.NeoSoft.com - (

Hope that's what you wanted.

-- 
Bruce...        pihlab_at_cbr.hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************