| 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.
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
-------------------------------------------------------------------------------