-----------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
3 - filter("B"."NAME"='sss')
4 -
access("A"."RN"="B"."RN")
18
rows selected.
非in-list和嵌套循环操作，调整这个参数时，不会影响oracle优化器成本的运算，如下
SQL>
set linesize 200 pagesize 999
SQL>
explain plan
for
select *
from t
where rn<200;
Explained.
SQL>
select *
from
table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash
value: 470836197
-------------------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 199 | 4179 | 3 (0)| 00:00:35 |
| 1 |
TABLE
ACCESS
BY
INDEX ROWID| T | 199 | 4179 | 3 (0)| 00:00:35 |
|* 2 |
INDEX RANGE SCAN | T_IDX | 199 | | 2 (0)| 00:00:24 |
-------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 -
access("RN"<200)
14
rows selected.
SQL>
alter
session
set optimizer_index_caching=1;
Session altered.
SQL>
explain plan
for
select *
from t
where rn<200;
Explained.
SQL>
select *
from
table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash
value: 470836197
-------------------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 199 | 4179 | 3 (0)| 00:00:35 |
| 1 |
TABLE
ACCESS
BY
INDEX ROWID| T | 199 | 4179 | 3 (0)| 00:00:35 |
|* 2 |
INDEX RANGE SCAN | T_IDX | 199 | | 2 (0)| 00:00:24 | --可以看到cost确实没有改变
-------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 -
access("RN"<200)
14
rows selected.
|