Re: Questions in Building Indexes

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

Because to resolve any query using a bitmap index, you simply retireve the relevant bitmaps, and then apply some boolean maths to them: Blue = 11001010 Tall = 00101010

Blue AND Tall = 00001010 -two records found (1+1 = 1, 0+1=0, 1+0=0, 0+0=0) Blue OR Tall = 11111111 - 8 records found (1+1=1, 0+1=1, 1+0=1, 0+0=0)

Since you've already retrieved the relevant bitmap in each case, the different results are gained by simply the changing the boolean operation.

Compare that with a b-tree index, where a request for "Andy" OR "Zebedee" would require you to navigate down one side of the index to find all Andy's, and then start again at the top to navigate down the other side to retrieve all Zebedees. That's actual physical I/O being done (potentially, at least), and hence b-trees are not terribly good at OR tests.

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

Ignore that old nonsense. Just keep ALL extent sizes within any given tablespace the same for all segments, and fragmentation simply won't happen. By default, Oracle does round extents up to multiples of 5 blocks, but if you use the MINIMUM EXTENT clause in Oracle 8.0, that's not true. And if you use the 8i Locally Managed Tablespace feature, it's also not true.

> Why bitmap index cannot be unique?
>

Come on!!! How unique can a series of 1's and 0's be?????!

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

You can modify PCTFREE, and you do so to try and avoid block splitting. So on a monotonically incrementing sequence number (ie, the numbers only ever go up), you can set it to zero and forget about it. For all other indexes where new data may arrive that has to be slotted within existing data, then block splitting is a pain, and a relatively high PCTFREE can minimise the pain.

PCTUSED cannot be set for indexes. It is set to zero and cannot be changed (in other words, following a massive delete of table records, an index leaf node may contain just a single remaining entry -but that entry must still be maintained in its correct position within the index, and no new entries are permitted into that leaf node until it has been entirely cleared of all existing entries.

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

Rebuilding an index means that the source TABLE is locked from all DML for the duration of the build, and that could mean locking users out of doing useful work for a long time. That's what an 'offline' rebuild means... just that DML on the table is not permitted until the rebuild completes. Nothing to do with offlining tablespaces or datafiles.

New in 8i was the concept of an online rebuild, where Oracle will store in a temporary log all the DML that arrives at the table whilst the index is being rebuilt. So from the User's perspective, the table is open for DML business as usual. When the index has been rebuilt, the temporary log is then played to the new index, and the effects of the DML on the table are thus incorporated within the index. Theoretically.

The one slight twist is that whilst the log is being played, the table is still subject to further DML, and that too has to be put into the temporary log. If you can clear out the log faster than new entries are added to it, fine. The thing will work nicely. But there comes a point where Oracle will realise that the log is not being cleared in a timely fashion -at which point, the table is locked from all further DML activity anyway, thus allowing the log to be cleared.

Regards
HJR
> Thx a lot
>
>
>
>
>
Received on Thu Jun 28 2001 - 19:11:14 CDT