A bitmap join index is a bitmap index for the join of two or more tables. For each value in a table column, the index stores the rowid of the corresponding row in the indexed table. In contrast,
a standard bitmap index is created on a single table.
A bitmap join index is an efficient means of reducing the volume of data that must be joined by performing restrictions in advance. For an example of when a bitmap join index would be useful,
assume that users often query the number of employees with a particular job type. A typical query might look as follows:
SELECT COUNT(*)
FROM employees, jobs
WHERE employees.job_id = jobs.job_id
AND jobs.job_title = 'Accountant';
The preceding query would typically use an index on jobs.job_title to retrieve the rows for Accountant and then the job ID, and an index on employees.job_id to find the matching rows. To retrieve the data from the index itself rather than from a scan
of the tables, you could create a bitmap join index as follows:
CREATE BITMAP INDEX employees_bm_idx
ON employees (jobs.job_title)
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;
As illustrated in Figure 3–2, the index key is jobs.job_title and the indexed table is employees.
Conceptually, employees_bm_idx is an index of the jobs.title column in the SQL query shown in Example 3–5 (sample output included). The job_title key in the index points to rows in the employees
table. A query of the number of accountants can use the index to avoid accessing the employees and jobs tables because the index itself contains the requested information.
Example 3–5 Join of employees and jobs Tables
SELECT jobs.job_title AS "jobs.job_title", employees.rowid AS "employees.rowid"
FROM employees, jobs
WHERE employees.job_id = jobs.job_id
ORDER BY job_title;
In a data warehouse, the join condition is an equijoin (it uses the equality operator) between the primary key columns of the dimension tables and the foreign key columns in the fact table.
Bitmap join indexes are sometimes much more efficient in storage than materialized join views, an alternative for materializing joins in advance.
Bitmap Storage Structure
Oracle Database uses a B-tree index structure to store bitmaps for each indexed key. For example, if jobs.job_title is the key column of a bitmap index, then the index data is stored in one
B-tree. The individual bitmaps are stored in the leaf blocks.
Assume that the jobs.job_title column has unique values Shipping Clerk, Stock Clerk, and several others. A bitmap index entry for this index has the following components:
■The job title as the index key
■A low rowid and high rowid for a range of rowids
■A bitmap for specific rowids in the range
Conceptually, an index leaf block in this index could contain entries as follows:
Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001
.
.
.
The same job title appears in multiple entries because the rowid range differs.
Assume that a session updates the job ID of one employee from Shipping Clerk to Stock Clerk. In this case, the session requires exclusive access to the index key entry for the old value (Shipping Clerk) and the new value (Stock Clerk). Oracle Database
locks the rows pointed to by these two entries—but not the rows pointed to by Accountant or any other key—until the UPDATE commits.
The data for a bitmap index is stored in one segment. Oracle Database stores each bitmap in one or more pieces. Each piece occupies part of a single data block.
分享到:
相关推荐
Oracle's handling of bitmap join indexes
现象:创建Bitmap Join Indexes时出现ORA-25954报错: 维的主键或唯一约束条件缺失。 53vi.Com 原因:受到约束与索引的影响。
而其中索引的使用又是经常碰到的一个调整优化的难题, 经常听到很多itpub上的同仁讨论使用bitmap index但是又不敢轻易使用它(害怕出现性能问题)。这里参考了Oracle文档,《Beginning Oracle Programming》及《Oracle...
horizontal partitioning of tables and bitmap join indexes. The partitioning schema viii Preface and the set of bitmap indexes are selected by means of genetic and greedy algorithms. The proposed ...
国外的英文版二进制资源占用PPT分享文档,说明了什么是二进制索引,什么存储,什么检索,有什么样的解决方案
Sorting improves word-aligned bitmap indexesDaniel Lemirea,∗, Owen Kaserb, Kamel AouicheaaLICEF, Université du Québec à Montréal (UQAM), 100 Sherbrooke West, Montreal, QC, H2X 3P2 CanadabDept...
软件开发网在此之前给大家介绍过图片加载框架Glide的基本用法介绍,大家可以先参考一下,本篇内容更加深入的分析了Glide获取图片Path、Bitmap用法,以及实现的代码分析。 1. 获取Bitmap: 1)在图片下载缓存好之后...
处理bitmap内存溢出问题
赠送jar包:RoaringBitmap-0.7.45.jar; 赠送原API文档:RoaringBitmap-0.7.45-javadoc.jar; 赠送源代码:RoaringBitmap-0.7.45-sources.jar; 赠送Maven依赖信息文件:RoaringBitmap-0.7.45.pom; 包含翻译后的API...
机器视觉技术必备,从位图到halcon图像,bitmap图像转为halcon图像,bitmap to Hobject
android bitmap outofMemory 用来解决android中常见的bitmap outOfMemory
改用原始图像数据流回调函数RegisterImageStreamCallback进行抓图 但回调函数帧为YUV420格式 需要转成Bitmap才用用于显示及本地查看 在网上找了好多都是基于R Y+1 4075 V 128 G Y 0 3455 U 128 – 0 7169 V 128 B Y+...
Halcon_Bitmap转换方法 写的文档 开发笔记
C#语言下,官方好像没有提供直接的方式把Bitmap转为Mat,但是可以使用我的这个方式转变格式
赠送jar包:RoaringBitmap-0.7.45.jar; 赠送原API文档:RoaringBitmap-0.7.45-javadoc.jar; 赠送源代码:RoaringBitmap-0.7.45-sources.jar; 赠送Maven依赖信息文件:RoaringBitmap-0.7.45.pom; 包含翻译后的API...
c#.net Bitmap类的基本使用方法
一个在VC中将Bitmap转换为Byte[]的小例子。
3个bitmap文件,用于认识bitmap文件格式的本质