Relational Database Index Design and the Optimizers

abstractcyj發表於2013-05-08
DatabaseIndex
1. Focus on the maor issues
    It is vital to focus on the few really major issues,
not on the relatively unimportant detail under which many people drown. This is
key—to focus on a very few, crucially important areas—and to be able to say
how long it would take or how much it would cost.

2. The size of the table pages sets an upper limit to the length of table rows. Normally, a table row must fit in one table page; an index row must fit in one leaf page. If the average length of the rows in a table is more than one third of the page size, space utilization suffers. Only one row with 2100 bytes fits in a 4K page, for instance. The problem of unusable space is more pronounced with indexes. As new index rows must be placed in a leaf page according to the index key value, the leaf pages of many indexes should have free space for a few index rows, after load and reorganization. Therefore, index rows that are longer than 20% of the leaf page may result in poor space utilization and frequent leaf page splits. 

3. Bitmap indexes consist of a bitmap (bit vector) for each distinct column value. Each bitmap has one bit for every row in the table. The bit is on if the related row has the value represented by the bitmap.
    Bitmap indexes make it feasible to perform. queries with complex and unpredictable compound predicates against a large table. This is because ANDing and ORing bitmap indexes is very fast, even when there are hundreds of millions of table rows. The corresponding operation with B-tree indexes requires collecting a large number of pointers and sorting large pointer sets.
    On the other hand a B-tree index, containing the appropriate columns, eliminates table access. This is important because random I/Os to a large table are very slow(about 10ms). With a bitmap index, the table rows must be accessed unless the SELECT list contains only COUNTs. Therefore, the total execution time using a bitmap index may be much longer than with a tailored, (fat) B-tree index. 
    Bitmap indexes should be used when the following conditions are true:
     1. The number of possible predicate combinations is so large that designing adequate B-tree indexes is not feasible.
     2. The simple predicates have a high filter factor, but the compound predicate(WHERE clause) has a low filter factor - or the SELECT list contains COUNTs only.
     3. The updates are batched(no lock contention). 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-760171/，如需轉載，請註明出處，否則將追究法律責任。

相關文章