Re: 10gR1 new feature: "Enhanced Bitmap Index Performance"

"ErikYkema" <erik.ykema_at_gmail.com> wrote in message news:1174049969.445437.55170_at_e65g2000hsc.googlegroups.com...
> On Mar 16, 7:55 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>> One of the points on the following blog entry answers your
>> question. The index does not acquire a huge trail of "old"
>> blocks as it used to with small updates before 10g.
>>
>> http://jonathanlewis.wordpress.com/2006/12/19/mything-in-action/
>>
>> --
>> Regards
>>
>> Jonathan Lewis
>
> Thanks Jonathan.
>
> Another statement that puzzles me (http://download-west.oracle.com/
> docs/cd/B10501_01/server.920/a96533/data_acc.htm#2432)
> "Index maintenance is deferred until the end of each DML
> operation. ... For example, when you insert 1000 rows, the inserted
> rows are placed into a sort buffer and then the updates of all 1000
> index entries are batched. (This is why SORT_AREA_SIZE must be set
> properly for good performance with inserts and updates on bitmap
> indexes.) Thus, each bitmap segment is updated only once in each DML
> operation, even if more than one row in that segment changes."
>
> Does this mean that there is no reason at all to set my BMIs to
> unusable and rebuild them afterwards, if I am using bulk operations?
> (Bulk meaning direct path inserts and/or?? forall based pl/sql bulk
> inserts?)
>
> Regards, Erik
>

You may find that you can do this fairly safely with some bitmap indexes, but not with others. It depends on the number of rows in the table, the number of rows per value, the pattern of distribution of values, and the fraction of data that appears in each bulk load.

If you do try it, you may want to set pctfree on the indexes to 67%, as this can avoid some problems of block splits and index growth - which can still be fairly heavy even with bulk loads.

A compromise may be viable - load daily without dropping, but drop and rebuild once per week.

You may also find that some indexes benefit from a coalesce after load.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html