Re: Questions in Building Indexes

Answers mixed with questions:

"Victor" <lch_1_at_hotmail.com> wrote in message news:<9hcq4s$qo56_at_imsp212.netvigator.com>...
> 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?
>

This topic is covered excellently in the Concepts maual. But basically rather than fetch the rows and test the conditions Oracle can fetch from the index blocks where 1 index entry can cover a range of table rows that have the same value and test index block bitmaps against each other to find rowids that match multiple where clause conditons before having to fetch the table block.  

> Why using a few standard extent sizes that are mulitples of 5 xDB_BLOCK_SIZE
> can minimize fragmentation?

Because Oracle likes to round table allocations in even multiples of 5 and if the extent being freed by an index rebuild, moving or dropping a table etc... frees a chunk of space that is the same size or a multiple of the size another objects wants to use to extend then the chance of reuse is higher. The more reuse the less wasted space.

By the way the default tablespace storage initial extent size is 5 Oracle data blocks when uniform extents are not specified. Uniform extents, new with 8.1, make enforcement of one extent size for the entire tablespace possible even if the Create table/index SQL specifies a storeage clause.

> Why bitmap index cannot be unique?
>

It would pretty well defeat the purpose of bitmap indexes if the data being indexed was unique. You want only a few data values to exist for the column so that one or two bits can be used to represent all possible values for the column. See the Concepts manual for more detail.

> can I modify the PCTFREE and PCTUSED parameter of INDEX?
>

Indexes do not have a pctused parameter, only tables. You can alter the values for the parameters on tables after object creation, but existing data blocks are not affected until the next space operation affected by the parameter takes place on the block. You will get an ORA-02243 if you try to alter the pctfree on an 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?
>

Basically yes. The default is offline. You can also drop and recreate the index.

> Thx a lot

• Mark D Powell --