Plan hash value: 3506647781
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 252 | 3 (0)| 00:00:31 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 12 | 252 | 3 (0)| 00:00:31 |
|* 3 | INDEX RANGE SCAN | T_IDX | 12 | | 2 (0)| 00:00:19 | --可以看到cost确实降低了
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("RN"=1 OR "RN"=2 OR "RN"=3 OR "RN"=4 OR "RN"=5 OR "RN"=6 OR
"RN"=7 OR "RN"=100 OR "RN"=130 OR "RN"=200 OR "RN"=240 OR "RN"=2000)
16 rows selected 嵌套循环连接示例： SQL> explain plan for select /*+ use_nl(a b) */ * from t a,t b where a.rn=b.rn and b. name='sss';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 752965310
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 8 (0)| 00:01:34 |
| 1 | NESTED LOOPS | | | | | |
|* 3 | TABLE ACCESS FULL | T | 1 | 21 | 6 (0)| 00:01:10 |
|* 4 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:12 |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 21 | 2 (0)| 00:00:24 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."NAME"='sss')
4 - access("A"."RN"="B"."RN")
18 rows selected.
SQL> alter session set optimizer_index_caching=10;
Session altered.
SQL> explain plan for select /*+ use_nl(a b) */ * from t a,t b where a.rn=b.rn and b. name='sss';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 752965310
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 7 (0)| 00:01:22 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 42 | 7 (0)| 00:01:22 | --可以看到cost确实降低了
|* 3 | TABLE ACCESS FULL | T | 1 | 21 | 6 (0)| 00:01:10 |
|* 4 | INDEX RANGE SCAN | T_IDX | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 21 | 1 (0)| 00:00:12 |
---------------------------------------------