oracle的hint探讨--add_equal,index_join,index_combine

and equal index join index combine 这三种都是 oracle 利用索引关联获得数据的方法 三者的 目的都是为了最大限度的利用索引 减少回表的代价 但是三者的实现方法是有区别的 下面一一来分析 and equal and equal 这种方式需要查询条件里面包括所有索引列 然后取得每个索引中得到的 rowid 列表 然后对这些 列表做 merge join 过滤出相同的 rowid 后再去表中获取数据或者直接从索引中获得数据 and equal 有一些限制 比如它只对单列索引有效 只对非唯一索引有效 使用到的索引不能超过 5 个 查询条件只能是 在 10g 中 and equal 已经被废弃了 只能通过 hint 才能生效 涉及索引满足涉及索引满足 and equaland equal 的限制条件的情况的限制条件的情况 createcreate tabletable test asas selectselect fromfrom dba objects createcreate indexindex ind test owner onon test owner createcreate indexindex ind test object name onon test object name SELECTSELECT and equal test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST 由于涉及到的索引都为单列索引 且所有索引都在查询中出现 谓词也都是使用 关系符号连接 所以我 们可以看到 add equal 索引生效 执行计划如下所示 Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 29 2 0 1 AND EQUAL 2 INDEX RANGE SCAN IND TEST OWNER 1 1 0 3 INDEX RANGE SCAN IND TEST OBJECT NAME 2 1 0 如果查询条件只包含如果查询条件只包含 ownerowner SELECTSELECT and equal test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER TEST 本例中只使用到了 hint 中的一个索引关联的列 owner 所以导致 and equal 提示无效 执行计划中只使 用了一个索引 Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 29 2 0 1 TABLE ACCESS BY INDEX ROWID TEST 1 29 2 0 2 INDEX RANGE SCAN IND TEST OWNER 1 1 0 查询条件是查询条件是 的情况的情况 SELECTSELECT and equal test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST 本例中由于查询谓词中对列object name使用了 符号 所以也会导致and equal提示无效 Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 40 2 0 1 TABLE ACCESS BY INDEX ROWID TEST 1 40 2 0 2 INDEX RANGE SCAN IND TEST OBJECT NAME 2 1 0 查询条件是查询条件是inin的情况的情况 SELECTSELECT and equal test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER ININ TEST zhanglei ANDAND OBJECT NAME ININ TEST In的情况有点特殊 如果in中的可选值有两个 那么将会导致add equal提示失效 但如果in后面的 可选值只有一个 oracle会将in当作 号来处理 同样可以使用and equal提示 Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 40 2 0 1 TABLE ACCESS BY INDEX ROWID TEST 1 40 2 0 2 INDEX RANGE SCAN IND TEST OBJECT NAME 2 1 0 index join index join index join 顾名思义是对 index 进行关联 oracle 通过 hash index join 的方式实现了避免对表 的访问 所有的数据都从索引中直接获得 它不受查询条件影响 可以是唯一索引 也可以是多列索引 SELECTSELECT index join test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST 由对定义的描述我们可以知道 index join 这个 hint 的主要功能是通过对表索引的 hash join 操作获 得所需要的数据 从而避免回表执行查询 针对源表数据较大 而返回结果数据都可以在索引中满足的情 况 这个 hint 比较有效 Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 29 3 34 1 VIEW index join 001 1 29 3 34 2 HASH JOIN 3 INDEX RANGE SCAN IND TEST OWNER 1 29 1 0 4 INDEX RANGE SCAN IND TEST OBJECT NAME 1 29 1 0 可以不带查询条件可以不带查询条件 我们甚至可以为不带查询条件的检索语句使用 index join 的提示 只不过由 index range scan 变 成了 index fast full scan SELECTSELECT index join test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 51984 1472K 478 2 1 VIEW index join 001 51984 1472K 478 2 2 HASH JOIN 3 INDEX FAST FULL SCAN IND TEST OWNER 51984 1472K 153 2 4 INDEX FAST FULL SCAN IND TEST OBJECT NAME 51984 1472K 322 1 返回结果包括非索引数据列返回结果包括非索引数据列 如果不是所有数据都能从索引获得 那么将不会使用 index join SELECTSELECT index join test ind test owner ind test object name OWNER OBJECT NAME object type FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 40 2 0 1 TABLE ACCESS BY INDEX ROWID TEST 1 40 2 0 2 INDEX RANGE SCAN IND TEST OWNER 1 1 0 index combine index combine index combine 最早是用在 bitmap index 上的 在 9i 开始 oracle 默认可以使用在 btree 索引上 这是由 b tree bitmap plans 参数来控制的 oracle 将 btree 索引中获得的 rowid 信息通过 BITMAP CONVERSION FROM ROWIDS 的步骤转换成 bitmap 进行匹配 然后匹配完成后通过 BITMAP CONVERSION TO ROWIDS 再转换出 rowid 获得数据或者回表获得数据 SELECTSELECT index combine test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 29 2 0 1 BITMAP CONVERSION TO ROWIDS 1 29 2 0 2 BITMAP AND 3 BITMAP CONVERSION FROM ROWIDS 4 INDEX RANGE SCAN IND TEST OWNER 1 0 5 BITMAP CONVERSION FROM ROWIDS 6 INDEX RANGE SCAN IND TEST OBJECT NAME 1 0 回表取数据的情况回表取数据的情况 SELECTSELECT index combine test ind test owner ind test object name OWNER OBJECT NAME OBJECT TYPE FROMFROM TEST WHEREWHERE OWNER TEST ANDAND OBJECT NAME TEST Execution Plan Id Operation Name Rows Bytes Cost CPU 0 SELECT STATEMENT 1 40 2 0 1 TABLE ACCESS BY INDEX ROWID TEST 1 40 2 0 2 BITMAP CONVERSION TO ROWIDS 3 BITMAP AND 4 BITMAP CONVERSION FROM ROWIDS 5 INDEX RANGE SCAN IND TEST OWNER 1 0 6 BITMAP CONVERSION FROM ROWIDS 7 INDEX RANGE SCAN IND TEST OBJECT NAME 1 0 不带查询条件的情况不带查询条件的情况 index combine 将不被使用 SELECTSELECT index combine test ind test owner ind test object name OWNER OBJECT NAME FROMFROM TEST Execu