关于optimizer_index_caching，在《基于成本的oracle优化法则》一书中作者提到过：

optimizer_index_caching is used to adjust the cost calculation for index blocks of the inner table in
nested loops and for the index blocks used during in-list iterators. It is not used in the calculation of costs for
simple index unique scans or range scans into a single table.

意思是说该参数只对in-list和嵌套循环连接中涉及到的索引扫描成本有影响，进行如下测试：

1.对in-list操作时索引成本计算的影响

SQL> alter session set optimizer_index_caching=0;

Session altered.

SQL> select * from t2 where name in ('1','2','3');

Execution Plan
----------------------------------------------------------
Plan hash value: 1588593371

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    36 |   288 |    42   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2          |    36 |   288 |    42   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T2_NAME |    36 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("NAME"='1' OR "NAME"='2' OR "NAME"='3')

SQL> alter session set optimizer_index_caching=100;

Session altered.

SQL> select * from t2 where name in ('1','2','3');

Execution Plan
----------------------------------------------------------
Plan hash value: 1588593371

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    36 |   288 |    37   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |             |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2          |    36 |   288 |    37   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T2_NAME |    36 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("NAME"='1' OR "NAME"='2' OR "NAME"='3')

2.对嵌套循环连接时索引扫描成本的影响。

SQL> alter session set optimizer_index_caching =0;

Session altered.

SQL> select /*+ use_nl(t1,t2) ordered */ t1.id,t1.join,t2.name from t1,t2 where t1.join=t2.join and t2.name='1';

Execution Plan
----------------------------------------------------------
Plan hash value: 1113340891

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   200 |  2800 |   753   (0)| 00:00:10 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2          |     4 |    32 |    15   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |             |   200 |  2800 |   753   (0)| 00:00:10 |
|   3 |    TABLE ACCESS FULL        | T1          |    50 |   300 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX_T2_NAME |    12 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."JOIN"="T2"."JOIN")
   4 - access("T2"."NAME"='1')

SQL> alter session set optimizer_index_caching =100;

Session altered.

SQL> select /*+ use_nl(t1,t2) ordered */ t1.id,t1.join,t2.name from t1,t2 where t1.join=t2.join and t2.name='1';

Execution Plan
----------------------------------------------------------
Plan hash value: 1113340891

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   200 |  2800 |   653   (0)| 00:00:08 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T2          |     4 |    32 |    13   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |             |   200 |  2800 |   653   (0)| 00:00:08 |
|   3 |    TABLE ACCESS FULL        | T1          |    50 |   300 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | IDX_T2_NAME |    12 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."JOIN"="T2"."JOIN")
   4 - access("T2"."NAME"='1')

3.对索引范围扫描成本的计算

SQL> alter session set optimizer_index_caching =0;

Session altered.

SQL> select /*+ index(a,idx_t2_name) */ * from t2 a where name>'1000';

Execution Plan
----------------------------------------------------------
Plan hash value: 37276610

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1196K|  9351K|  1212K  (1)| 04:02:31 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2          |  1196K|  9351K|  1212K  (1)| 04:02:31 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2_NAME |  1200K|       | 12359   (1)| 00:02:29 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME">'1000')

SQL> alter session set optimizer_index_caching =100;

Session altered.

SQL> select /*+ index(a,idx_t2_name) */ * from t2 a where name>'1000';

Execution Plan
----------------------------------------------------------
Plan hash value: 37276610

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1196K|  9351K|  1212K  (1)| 04:02:31 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2          |  1196K|  9351K|  1212K  (1)| 04:02:31 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2_NAME |  1200K|       | 12359   (1)| 00:02:29 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME">'1000')

很明显，对于in-list和嵌套循环连接，设置了 optimizer_index_caching 为非0后，索引范围扫描的成本都变成了0，而对于单纯的索引范围扫描来说，该参数的设置是无效的。