Oracle Index Merge 与 and_equal 的变迁
浏览:1470次 出处信息
同时and_equal可以通过Hints来进行强制,最少指定两个索引,最多指定5个。
以下的执行计划是常见的and_equal执行方式:
SQL> select /*+ and_equal(t1 iu ii) */ username,password from t1 where username='EYGLE' and user_id=58;但是从Oracle 10g开始,and_equal操作被废弃(depricated)掉,Oracle不再支持。这里废弃的含义并不是被彻底移除,而是说不再进行改进,通过hints仍然可以强制实现Index Merge的and_equal操作。
USERNAME PASSWORD
------------------------------ ------------------------------
EYGLE B726E09FE21F8E83
执行计划
----------------------------------------------------------
Plan hash value: 1425017857
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | AND-EQUAL | | | | | |
|* 3 | INDEX RANGE SCAN | II | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IU | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("USERNAME"='EYGLE' AND "USER_ID"=58)
3 - access("USER_ID"=58)
4 - access("USERNAME"='EYGLE')
以上的输出就是来自Oracle 10.2.0.4,在使用rule提示时,Oracle会主动选择and_equal,所以不再支持是因为,这种方法限制过多,而且远不如复合索引来的灵活:
SQL> select * from v$version;新的可选替代方案是NDEX_COMBINE,从9i开始,初始化参数 _b_tree_bitmap_plans设置为true,允许为B*Tree索引使用,bitmap convert:
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select /*+ rule */ username,password from t1 where username='EYGLE' and user_id=58;
USERNAME PASSWORD
------------------------------ ------------------------------
EYGLE B726E09FE21F8E83
执行计划
----------------------------------------------------------
Plan hash value: 3072843751
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | AND-EQUAL | |
|* 3 | INDEX RANGE SCAN | IU |
|* 4 | INDEX RANGE SCAN | II |
--------------------------------------------
SQL> select /*+ index_combine(t1 iu ii) */ username,password from t1 where username='EYGLE' and user_id=58;
USERNAME PASSWORD
------------------------------ ------------------------------
EYGLE B726E09FE21F8E83
执行计划
----------------------------------------------------------
Plan hash value: 1808973554
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 47 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | II | | | 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IU | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("USER_ID"=58)
7 - access("USERNAME"='EYGLE')
建议继续学习:
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:oracle 子查询写法
后一篇:Oracle数据库性能模型 >>
文章信息
- 作者:eygle 来源: Oracle Life
- 标签: and_equal Merge
- 发布时间:2010-06-21 17:30:28
近3天十大热文
- [70] Twitter/微博客的学习摘要
- [65] find命令的一点注意事项
- [64] 如何拿下简短的域名
- [64] IOS安全–浅谈关于IOS加固的几种方法
- [63] android 开发入门
- [62] 流程管理与用户研究
- [62] Go Reflect 性能
- [60] Oracle MTS模式下 进程地址与会话信
- [59] 读书笔记-壹百度:百度十年千倍的29条法则
- [59] 图书馆的世界纪录