今天下午部门一个兄弟做了一个关于index_join与injdex_combine区别的测试，引发我的兴趣， 找到一段Jonathan lewis在论坛的回复，简单翻译了下,并针对同事提供的说明做了一点补充与测试..
补充：刚刚看到Jonathan Lewis还有另外一篇关于index Combine的文章
http://nixforums.org/about109312-Hints-difference-between-AND_EQUAL–INDEX_JOIN-and-INDEX_.html
AND_EQUAL:
Can operate on 2 to 5 non-unique, single-column indexes with an equality condition on the indexed values. For colX = const each index produces a list of rowids which are guaranteed to be in rowid order, hence pre-sorted and ready for a merge join. rowids that survive the join are used to access the table
and-equal支持操作2-5个非唯一单列索引，这些索引都有一个基于索引值的等值查询条件。 对于colX = “常量”, 每个索引都生成一个排好了顺序的rowid列表, 可以直接进行合并连接. 符合连接条件的rowid用来访问表取得结果数据..
Index_join
Can be used to join any two indexes – no restrictions on number of columns, uniqueness, or predicates. Used to derive results without visiting the table at all. Each index is used to supply a (sub)set of its columns plus the relevant rowids. Oracle then performs a hash join on the two sets of data – using the rowid as the join column. Any data surviving the data is the answer set. Having joined two indexes by hash join, it is possible for Oracle to join a third, and so on until all the required columns have been join into the final result set
index join可以连接任意两个索引（没有任何限制，如列数量，索引唯一性，以及谓词等），以实现完全不需要访问表就得到结果集的查询。每个索引都提供结果集的一个完整或部分列的集合以及对应的rowid。Oracle接着对这两个数据集进行散列连接-使用rowid作为连接条件。连接得到的结果就是所需的结果集。对Oracle来讲, 可以对两个索引做散列连接, 自然也可以对三个索引进行连接, 一直到所有出现在select list的列都已经取到，从而得到最后需要的结果集。
Index combine
Used for bitmap operators (although you can reference b-tree indexes in the hint to indicate to Oracle
that the index is a candidate for ‘rowid conversion to bitmap’).
Oracle acquires bitmaps from each index, and uses the AND, OR, NOT, or MERGE operators to produce
a result bit string that can then be converted into rowids for visiting the table.
index_combine 主要用来处理bitmap操作(虽然, 也可以在提示中引用B-Tree索引来强制Oracle将这个B-Tree索引作为
“rowid conversion to bitmap”的一个候选项). Oracle会获取每个索引的bitmap,并使用AND/OR/NOT/MERGE
等位图操作来生成一个可转换成rowid的位串, 以使用这些rowid来访问数据表.
根据Joanthan Lewis的这份说明, 我个人的理解是, index join只会处理能够返回完整结果集的查询, 而index combine支持这两类操作..
下面是相关的测试结果， 包含我同事提供的两个对比测试结果。
–这是我针对同事的理解做的测试..以验证index combine支持需要回表与不需要回表的操作，虽然在实际运行中这种情况可能出现的并不多
user@host>explain plan for
2 select /*+INDEX_COMBINE(member MEMBER_MID_IND MEMBER_COMPANY_IND)*/count(*)
3 from member
4 where company like :1
and member_id like :2; 5
Explained.
user@host>@?/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1195767410
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | | 4007 (2)| 00:00:49 |
| 1 | SORT AGGREGATE | | 1 | 27 | | | |
| 2 | BITMAP CONVERSION COUNT | | 17647 | 465K| | 4007 (2)| 00:00:49 |
| 3 | BITMAP AND | | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 5 | SORT ORDER BY | | | | 6280K| | |
|* 6 | INDEX RANGE SCAN | MEMBER_MID_IND | | | | 244 (1)| 00:00:03 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
| 8 | SORT ORDER BY | | | | 6280K| | |
|* 9 | INDEX RANGE SCAN | MEMBER_COMPANY_IND | | | | 278 (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("MEMBER_ID" LIKE :2)
filter("MEMBER_ID" LIKE :2 AND "MEMBER_ID" LIKE :2)
9 - access("COMPANY" LIKE :1)
filter("COMPANY" LIKE :1 AND "COMPANY" LIKE :1)
24 rows selected.
–这一段是我的同事提供的测试代码..在此谢谢了…
--index hash join select count(*) from member where company like :1 and member_id like :2; ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 27 | 1877 (1)| 00:00:23 | | 1 | SORT AGGREGATE | | 1 | 27 | | | |* 2 | VIEW | index$_join$_001 | 17647 | 465K| 1877 (1)| 00:00:23 | |* 3 | HASH JOIN | | | | | | |* 4 | INDEX RANGE SCAN| MEMBER_MID_IND | 17647 | 465K| 1970 (2)| 00:00:24 | |* 5 | INDEX RANGE SCAN| MEMBER_COMPANY_IND | 17647 | 465K| 2038 (2)| 00:00:25 | ------------------------------------------------------------------------------------------ --index combine select * from member where company like :1 and member_id like :2; --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 17647 | 5394K| | 9135 (1)| 00:01:50 | | 1 | TABLE ACCESS BY INDEX ROWID | MEMBER | 17647 | 5394K| | 9135 (1)| 00:01:50 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | | | 3 | BITMAP AND | | | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | | | 5 | SORT ORDER BY | | | | 6280K| | | |* 6 | INDEX RANGE SCAN | MEMBER_MID_IND | 63530 | | | 244 (1)| 00:00:03 | | 7 | BITMAP CONVERSION FROM ROWIDS| | | | | | | | 8 | SORT ORDER BY | | | | 6280K| | | |* 9 | INDEX RANGE SCAN | MEMBER_COMPANY_IND | 63530 | | | 278 (1)| 00:00:04 | --------------------------------------------------------------------------------------------------------------- 解释： --index hash join: 将两个index range scan得到的rowid结果集进行hash join，用于不需要回表的情况。 --index bitmap combine: 将两个index range scan得到的rowid结果集进行bitmap and，用于需要回表的情况。
No related posts.
最近评论