Re: Questions in Building Indexes

> Why Bitmap index is good for queries often use a combination of multiple
> WHERE conditions invloving the OR operator

Because OR (& AND for that matter) conditions on bitmaps are simple Boolean operations on bit-strings -- these are very efficient operations considering that *each bit* represents a row entry & boolean operations are very efficiently processed by the CPU (probably with a single instruction) so a lot of rows are processed for little cost in processing power, memory & I/O resources (the bitmaps are compressed).

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

The "5X" rule is used because, by default, Oracle rounds up an extent size request to a multiple of 5 database blocks. The reason for using a small set of "standard" extent sizes is to minimize fragmention wthin the *tablespace* ("external fragmentation") as extents are allocated & deallocated -- by using a small set, where ideally the large sizes are even multiples of the small sizes, you decrease the likelihood of creating unused "holes" in the tablespace.

> Why bitmap index cannot be unique?

Because then you'd have an entire bitstring (with one bit for every row in table) for *every row* -- it would be a tremndous waste of space & would be very slow to use. The whole point of a bitmap index is for *low-cardinality* columns -- ie, where each value for a column maps to a *lot* of rows (duplicates) in the table. For high cardinality data (of which UNIQUE is the limit) you want to use B*Trees.

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

I suppose, though the defaults are usually fine...

> 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?

Yes "offline" is the default & it means the table is not available for DML while the build is in progress. Received on Wed Jun 27 2001 - 10:42:05 CDT