207
DataBase Index
1. what is Database index?A database index is a data structure that improves the speed of operations on a database table. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.
An index
- is sorted by key values, (that need not be the same as those of the table)
- is small, has just a few columns of the table.
- refers for a key value to the right block within the table.
- speeds up reading a row, when you know the right search arguments.
2. Database Index Tips
- Put the most unique data element first in the index, the element that has the biggest variety of values. The index will find the correct page faster.
- Keep indexes small. It's better to have an index on just zip code or postal code, rather than postal code & country. The smaller the index, the better the response time.
- For high frequency functions (thousands of times per day) it can be wise to have a very large index, so the system does not even need the table for the read function.
- For small tables an index is disadvantageous. For any function the system would be better off by scanning the whole table. An index would only slow down.
- Index note:
- An index slows down additions, modifications and deletes. It's not just the table that needs an update, but the index as well. So, preferably, add an index for values that are often used for a search, but that do not change much. An index on bank account number is better than one on balance.
3.Index Implementations
3.1 The Oracle b-tree index
樹最底層的塊叫葉子節點，其中分別包括各個索引鍵以及一個rowid（指向索引的行），有意思的是，索引的葉子節點實際上構成了一個雙向鏈表，一旦發現要從葉節點中的哪裏“開始”，執行值的有序掃描（索引區間掃描index range scan）
就會很容易。我們不用再在索引結構中導航。
所以要滿足如下的謂詞條件將相當簡單：
where x between 20 and 30
Oracle 發現一個最小鍵值大於或等於20的索引葉子塊，然後水平地遍曆葉子節點鏈表，直到命中一個大於30的值。
3.2 Bitmapped indexes
Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed
在位圖結構中，一個二位數組中的一列被用來存儲被索引列的所有可能值
The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality.
For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves.
For example, assume we wanted to find old blue Toyota Corollas manufactured in 1981:
Create BITMAP index color_idx on vehicle(color);
如果更新一個位圖索引鍵，那麼這個鍵指向的數百條紀錄會與你實際更新的那一行一同被有效地鎖定。
最後更新：2017-04-02 00:06:40