Re: index Vs bitmap index

Using bitmapped indexes will reduce the concurrency of updates to the table that is indexes. When a user modifies a row with any type of DML, the affected values will be locked in the index. The index will not unlock until the transaction is commited or rolled back. This is am important fact to remember in deciding which one to use.

Jerry Gitomer wrote:
>
> 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 Wed Feb 25 1998 - 00:00:00 CST