Benefits for Data Warehousing Applications (221)

Bitmap indexing benefits data warehousing applications which have large amounts of
data and ad hoc queries but a low level of concurrent transactions. For such
applications, bitmap indexing provides:
■ Reduced response time for large classes of ad hoc queries
■ A substantial reduction of space use compared to other indexing techniques
■ Dramatic performance gains even on very low end hardware
■ Very efficient parallel DML and loads
Fully indexing a large table with a traditional B-tree index can be prohibitively
expensive in terms of space, because the index can be several times larger than the
data in the table. Bitmap indexes are typically only a fraction of the size of the indexed
data in the table.

Bitmap indexes are not suitable for OLTP applications with large numbers of
concurrent transactions modifying the data. These indexes are primarily intended for
decision support in data warehousing applications where users typically query the
data rather than update it.

Bitmap indexes are also not suitable for columns that are primarily queried with less
than or greater than comparisons. For example, a salary column that usually appears
in WHERE clauses in a comparison to a certain value is better served with a B-tree
index. Bitmapped indexes are only useful with equality queries, especially in
combination with AND, OR, and NOT operators.

Bitmap indexes are integrated with the Oracle optimizer and execution engine. They
can be used seamlessly in combination with other Oracle execution methods. For
example, the optimizer can decide to perform a hash join between two tables using a
bitmap index on one table and a regular B-tree index on the other. The optimizer
considers bitmap indexes and other available access methods, such as regular B-tree
indexes and full table scan, and chooses the most efficient method, taking parallelism
into account where appropriate.

Parallel query and parallel DML work with bitmap indexes as with traditional
indexes. Bitmap indexes on partitioned tables must be local indexes. Parallel create
index and concatenated indexes are also supported.

資料倉儲應用中點陣圖索引的獲益
1. 能夠減少大資料量特別查詢的響應時間
能夠節省大量儲存空間
顯著提高效能
有利於並行 DML 和並行載入
2. 點陣圖索引所佔的空間比被索引資料小得多
3. 點陣圖索引主要用於資料倉儲系統中的決策支援功能，在這種環境下使用者對資料的操作主要是查詢而非修改
4. 點陣圖索引適用於等值查詢，尤其是存在 AND，OR，和 NOT 等邏輯運算子的組合時
5. 點陣圖索引是整合在 Oracle 的最佳化器和執行引擎之中的。點陣圖索引也能夠和 Oracle 中的其他執行方法無縫地組合。
例如，最佳化器可以在利用一個表的點陣圖索引和另一個表的平衡樹索引對這兩張表進行雜湊連線。
最佳化器能夠在點陣圖索引及其他可用的訪問方法中選擇效率最高的方式，同時考慮是否適合使用並行執行。
6. 點陣圖索引如同常規索引一樣，可以結合並行查詢和並行 DML。建立在分割槽表上的點陣圖索引必須為本地索引

ad hoc ：adv. 特別

[@more@]