Re: Questions in Building Indexes

Some of your Bitmap questions can be answered by understanding - conceptually - what a bitmap index is ...

Effectively it can be considered a 'key value' plus a single 'Yes' or 'No' bit for each row in the table. So in a 10 row table, where a column can contain 'Color', the colors being 'Red', 'Green' and 'Blue', you would end up with 3 index entries looking like

Red YNNNYNNNNN (meaning rows 1 & 5 contain Red) GreenNYNNNNNYNN
Blue NNYYNYYNYY

In large tables, the index can take a lot of space (Oracle has worked out a space compression mechanism, but it still could be very large).

1. If you had the color set to unique, you would need 10 index entries, each with only 1 "Y" and all other rows set to "N" for that color. Very inefficient and generally handled faster using other mechanisms.
2. If your question is 'Red OR Green', you can efficiently combine those two indexes to end up with

ResultYYNNYNNYNN

HTH
/Hans

Victor wrote:

> Dear All,
>
> I got some questions in the topic Indexes:
>
> Why Bitmap index is good for queries often use a combination of multiple
> WHERE conditions invloving the OR operator?
>
> Why using a few standard extent sizes that are mulitples of 5 xDB_BLOCK_SIZE
> can minimize fragmentation?
>
> Why bitmap index cannot be unique?
>
> can I modify the PCTFREE and PCTUSED parameter of INDEX?
>
> Are there two methods of rebuilding index? Offline and online modes? Is that
> Specifying 'Online' in the statement means rebuild in online mode? and omit
> it means offline mode?
>
> Thx a lot
  Received on Wed Jun 27 2001 - 13:25:33 CDT