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));
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.
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.
Yorumlar