-------------------------------------------------------------------------------------------------------------------------
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 | | | | | |
| 2 | NESTED LOOPS | | 1 | 42 | 8 (0)| 00:01:34 |
|* 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 |
--------------------------------------------- |