OLTP sistemlerde Bitmap index kullanımının sakıncaları

Sorun: Test sisteminde sadece kendime ait 2 farklı transaction’ımla insert’ler yaparken alakasız şekilde locklanmadan dolayı işlem yapamadığımı fark ettim. Detayına bakınca Transaction Exclusive diyor fakat hiç bir trigger’ı olan bir tabloya sadece basit bir insert yaparken niye böyle bir lock oluşur? 


Sonuç: Bitmap indexleri OLTP’lerde kullanmayın-ki detayı aşağıda bulunan 2. senaryodaki gibi gereksiz locklara sebep olduğunu da görüyoruz-, Datawarehouse/raporlama veritabanlarında kullanabilirsiniz.(Aynı anda birden fazla transaction olduğu durumlarda sıkıntı oluşuyor)

 

Well, I will bring back some of my reading memories and few experiences here:

Btree indexes are ideal for OLTP transactions, why is that? because og UPDATES behavior. If you want to save some space with btree indexes try reducing the PCTFREE parameter (careful).

These indexes are designed for queries that will bring a small result of rows, for example, if you query a 15milllion table and expect 7 million (or even 1million) of rows oracle do not recomend to index the field. On the other hand if you query that table and expect only 100000 (or less) records (according to oracle) the index will be well used.

Bitmap Indexes are much smaller, in fact, the size og the BITMAP indexes vary depending the cardinality of the field. For example, if in your 15million rows the indexed field have only 2 different values the index will be smaller than if there are 10different values. So, bitmap indexes are designed for datawarehouse applications where indexed field have low cardinality and will be used for reporting and will expect as m uch rows as needed.Bitmap indexes tend to have a high locking behavior in OLTP environment, so, again, use only for datawarehousing/reporting databases. 


Kaynak: http://laurentschneider.com/wordpress/2007/03/why-is-bitmap-index-not-designed-for-oltp.html why is bitmap index not designed for OLTP. In case you do not know it yet, having a bitmap on columns like GENDER(male/female) is a very bad practice in OLTP, because each insert does lock the whole table


create table t(name varchar2(10), gender varchar2(10));

create bitmap index bi on t(gender);

+------------------------------+   +-------------------------------+ 
| Session 1                    |   | Session 2                     |
+------------------------------+   +-------------------------------+ 
| SQL> insert into t           |   |                               |
|  2   values('JO','MALE');    |   |                               |
|                              |   |                               |
| 1 row created.               |   |                               |
|                              |   |                               |
|                              |   | SQL> insert into t            |
|                              |   |  2   values('JANE','FEMALE'); |
|                              |   |                               |
| SQL> commit;                 |   |                               |
|                              |   |                               |
| Commit complete.             |   |                               |
|                              |   |                               |
|                              |   | 1 row created.                |
|                              |   |                               |
+------------------------------+   +-------------------------------+


A pending transaction is blocking a portion of the index. So session 2 has to wait for transaction 1 to complete.


Kaynak: http://richardfoote.wordpress.com/2010/06/01/bitmap-index-degradation-since-10g-fix-you/


That said, Bitmap indexes are still unsuitable in OLTP type environments (even in 11g)  due to the locking implications associated with them. Perhaps a discussion for another day.That said, Bitmap indexes are still unsuitable in OLTP type environments (even in 11g)  due to the locking implications associated with them. Perhaps a discussion for another day.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s