Re: Dense and Sparse Index

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Mon, 17 May 2004 22:14:57 +1000
Message-ID: <40a8acb2$0$31674$afc38c87@news.optusnet.com.au>

Howard J. Rogers wrote:

>>         I would like to know when does dense index and
>> sparse index get built up in oracle. Are there any
>> expilict keywords for these index constructions?
>> I have read that dense index is faster with integer columns
>> and sparse index for varchar2 columns. Please clarify me.
>>

>
> Are these terms you've used in another database product? They are not
> terms I'm familiar with in an Oracle context, except in a purely
> theoretical sense. There's certainly no explicit keywords to use to
> control the matter. Unless it happens to be a 10g new feature I'm
> unaware of (joking: it's not).
>

Pure guess:

I think what the OP meant is range of values of index are either "sparse" or "dense". A numeric key would (normally) be more dense in the case of a monotonically increasing value. There would be less "holes" in possible values not being represented in index entries. A char key would not, unless someone wrote all possible combinations of chars, one for each row.

Similar to sparse and dense arrays in PL/SQL, I guess. If so, then it has NOTHING to do with the index nature and creation and ALL to do with the nature of the data and key values to be indexed. And there would be no specific create options for such indexes in Oracle. They are all indexed the same way. A "sparse index" uses only the necessary space for its available values. So does a "dense index". In fact, in Oracle the distinction when applied to indexes is meaningless.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam
(who is totally pissed off at people still using
him as a reference for contracting work when he's
been out of it for 3.5 years!...)  (but that has
nothing to do with HJR, just venting!)

Received on Mon May 17 2004 - 07:14:57 CDT