Re: index Vs bitmap index

Hi Catherine,

Traditional Oracle indexes are based on B-tree concepts in which the index is stored in the form of a balanced tree in order to minimize the number of accesses required to obtain the desired record. For columns with large numbers of differing values, for example, telephone numbers, B-tree style indexes are very fast with the number of accesses being 2 to the nth (where 2 to the nth is the smallest power of 2 larger than the number of rows being indexed). When dealing with columns with a very limited number of values, for example, sex of respondent where three values (female, male, unknown) are required B-tree indexes are not very helpful. In fact Oracle will do a full table scan rather than use an index if the optimizer determines that more than 10% of the rows in the table will be returned by the query.

Bit-map indexes which are based on having one bit for each possible value that can occur in the column being indexed are intended for these types of data. Using the example above three bits would be used for each row in the table; the first three bits for the first row, the second three bits for the second row and so on. The appropriate bit of the trio would be set for each row. The data base engine can now select the appropriate rows from the index much more quickly than when using a B-tree index.

Regards

Jerry

Catherine Maxcy Chow wrote:
>
> Can anyone tell me what is the difference between the old index and the
> new bitmap index? Thanks.
>
> Catherine
Received on Tue Feb 24 1998 - 00:00:00 CST