----------------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 199 | 4179 | 2 (0)| 00:00:18 |
| 1 |
TABLE
ACCESS
BY
INDEX ROWID| T | 199 | 4179 | 2 (0)| 00:00:18 |
|* 2 |
INDEX RANGE SCAN | T_IDX | 199 | | 1 (0)| 00:00:12 |
-------------------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
2 -
access("RN"<200)
14
rows selected.
SQL>
alter
session
set optimizer_index_cost_adj=500;
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: 1601196873
-------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 199 | 4179 | 6 (0)| 00:01:10 |
|* 1 |
TABLE
ACCESS
FULL| T | 199 | 4179 | 6 (0)| 00:01:10 |
--------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - filter("RN"<200)
13
rows selected.
五、optimizer_index_caching参数示例
IN-LIST示例
SQL>
set linesize 200 pagesize 9999
SQL>
explain plan
for
select *
from t
where rn
in (1,2,3,4,5,6,7,100,130,200,240,2000);
Explained.
SQL>
select *
from
table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash
value: 3506647781
--------------------------------------------------------------------------------------
| Id |
Operation |
Name |
Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 12 | 252 | 4 (0)| 00:00:43 |
| 1 | INLIST ITERATOR | | | | | |
| 2 |
TABLE
ACCESS
BY
INDEX ROWID| T | 12 | 252 | 4 (0)| 00:00:43 |
|* 3 |
INDEX RANGE SCAN | T_IDX | 12 | | 3 (0)| 00:00:31 |
--------------------------------------------------------------------------------------
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>
alter
session
set optimizer_index_caching=10;
Session altered.
SQL>
explain plan
for
select *
from t
where rn
in (1,2,3,4,5,6,7,100,130,200,240,2000);
Explained.
SQL>
select *
from
table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------- |