有關Bitmap Join Indexes的精彩帖一
全文引用至
In Oracle8i performance improvements were made using materialized views to store the resulting rows
of queries. The benefits of this mechanism are still relevant, but a certain subset of the queries
used in a data warehouse may benefit from the use of Bitmap Join Indexes.
How It Works
In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where
each bit represents a row in the table. A '1' means that row contains that value, a '0' means it
doesn't.
Bitmap Join Indexes extend this concept such that the index contains the data to support the join
query, allowing the query to retrieve the data from the index rather than referencing the join
tables. Since the information is compressed into a bitmap, the size of the resulting structure is
significantly smaller than the corresponding materialized view.
Creation
The index is created with reference to the columns in the joined tables that will be used to support
the query. In the following example an index is created where the SALES table is joined to the
CUSTOMERS table:
CREATE BITMAP INDEX cust_sales_bji
ON sales(customers.state)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;
Since the CUSTOMERS.STATE column is referenced in the ON clause of the index, queries on the SALES
table that join to the CUSTOMERS table to retrieve the STATE column can do so without referencing
the CUSTOMERS table. Instead the data is read from the bitmap join index:
SELECT SUM(sales.dollar_amount)
FROM sales,
customer
WHERE sales.cust_id = customer.cust_id
AND customer.state = 'California';
When dealing with large datasets, this reduction in processing can be substantial.
Restrictions
Bitmap Join Indexes have the following restrictions:
* Parallel DML is currently only supported on the fact table. Parallel DML on one of the
participating dimension tables will mark the index as unusable.
* Only one table can be updated concurrently by different transactions when using the bitmap
join index.
* No table can appear twice in the join.
* You cannot create a bitmap join index on an index-organized table or a temporary table.
* The columns in the index must all be columns of the dimension tables.
* The dimension table join columns must be either primary key columns or have unique
constraints.
* If a dimension table has composite primary key, each column in the primary key must be part of
the join.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10599713/viewspace-983032/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [轉帖]基礎一:健康網站的關鍵指標有哪些?網站指標
- 一張圖搞定七種 JOIN 關係
- join、inner join、left join、right join、outer join的區別
- MySQL筆記-左連線的使用(left join有關聯的多表查詢)MySql筆記
- 【SQL】13 SQL 別名、SQL 連線(JOIN)、SQL INNER JOIN 關鍵字、SQL LEFT JOIN 關鍵字、SQL RIGHT JOIN 關鍵字、SQL FULL OUTER JSQL
- 翻譯(九)——Clustered Indexes: Stairway to SQL Server Indexes Level 3IndexAISQLServer
- Join關聯聯絡案例
- MySQL表關聯join方式MySql
- Bitmap ImageView大小的一些祕密View
- MySQL(12)---紀錄一次left join一對多關係而引起的BUGMySql
- android 關於Bitmap壓縮處理解析Android
- 《極樂迪斯科》:一款沒有戰鬥的RPG也足夠精彩
- [20180510]20 Indexes.txtIndex
- [20180713]關於hash join 測試中一個疑問.txt
- Mysql join 的本質,Nested Loop Join 和 Hash JoinMySqlOOP
- sql的left join 、right join 、inner join之間的區別SQL
- mysql中的left join、right join 、inner join的詳細用法MySql
- Android中的BitmapAndroid
- 《精彩絕倫的css》筆記《一》CSS筆記
- 有關Editor的記錄(一)
- 有關 ThreadLocal 的一切thread
- [20180705]關於hash join 2.txt
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQLSQLIndex
- 乾貨:Bitmap 複用時的一個異常
- c# svg轉Bitmap Bitmap轉ImageSourceC#SVG
- Clickhouse的bitmap函式函式
- 一張圖說明SQL的join用法SQL
- [轉帖]一個NAT問題引起的思考
- Jedis bitmap
- SQL JOIN 中 on 與 where 有何區別SQL
- [轉帖]晶片相關-- Cpu歷史--intel系列晶片Intel
- [轉帖]晶片相關-- Cpu歷史--AMD系列晶片
- MySQL 8 新特性之Invisible IndexesMySqlIndex
- Inner Join, Left Outer Join和Association的區別
- 一張圖看懂 SQL 的各種 join 用法SQL
- [轉帖]Redis相關的核心引數解釋與設定Redis
- 實現簡單的BitMap
- Redis的Bitmap、HyperLogLog和GeoRedis