WXH_TBD1,WXH_TBD2都根据dba_objects创建。
连接列上有索引。object_name上有索引。
以下是实际的执行计划。
SQL> select * from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id and a.object_name='sss';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 892375762
-------------------------------------------------
| Id | Operation | Name
-------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID | WXH_TBD2
| 2 | NESTED LOOPS |
| 3 | TABLE ACCESS BY INDEX ROWID| WXH_TBD1
|* 4 | INDEX RANGE SCAN | T2_NAME
|* 5 | INDEX RANGE SCAN | T_ID
-------------------------------------------------
为什么不是这个执行计划？什么地方做了优化吗？
执行计划
-------------------------------------------------
Plan hash value: 892375762
-------------------------------------------------
| Id | Operation | Name
-------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | NESTED LOOPS |
| 2 | TABLE ACCESS BY INDEX ROWID| WXH_TBD1
|* 3 | INDEX RANGE SCAN | T2_NAME
| 4 | TABLE ACCESS BY INDEX ROWID| WXH_TBD2
|* 5 | INDEX RANGE SCAN | T_ID
-------------------------------------------------
11G,又变样了。
-------------------------------------------------
| Id | Operation | Name
-------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | NESTED LOOPS |
| 2 | NESTED LOOPS |
| 3 | TABLE ACCESS BY INDEX ROWID| WXH_TBD1
|* 4 | INDEX RANGE SCAN | T2_NAME
|* 5 | INDEX RANGE SCAN | T_ID
| 6 | TABLE ACCESS BY INDEX ROWID | WXH_TBD2
-------------------------------------------------
[ 本帖最后由 wei-xh 于 2010-12-24 09:31 编辑 ]
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-682556/ ，如需转载，请注明出处，否则将追究法律责任。
