通过索引查询I/O次数问题

lanjuuff

测试了一晚上，下面是脚本：

1. drop table test1;
   
2. drop index ind_test1_col1_unq;
   
3. drop index ind_test1_col2_rev;
   
4. drop index ind_test1_col3;
   
5. drop index ind_test1_col4;
   
6. drop index index ind_test1_col5;
   
7. drop index ind_test1_col567;
   
8. drop index ind_test1_func;
   
9. drop table test1;
   
10. create table test1(col1 number(10),col2 number(10),col3 number(10),
    
11.         col4 number(10),col5 varchar2(20),col6 varchar2(20),
    
12.         col7 varchar2(30),col8 varchar2(30),col9 varchar2(50));
    
13. insert into test1(col1,col2,col3,col4,col5,col6,col7,col8,col9)
    
14. select rownum*7+1,rownum*3+1,rownum*5+1,trunc(dbms_random.value(1,10000)),
    
15.         owner,object_type,object_name,to_char(timestamp),
    
16.         dbms_random.string('p',trunc(dbms_random.value(1,50)))
    
17. from all_objects;
    
18. commit;
    
19. --分别验证唯一索引、反转索引、非唯一索引、位图索引、降序索引（多列）、函数索引
    
20. create unique index ind_test1_col1_unq on test1(col1);
    
21. create unique index ind_test1_col2_rev on test1(col2) reverse;
    
22. create index ind_test1_col3 on test1(col3);
    
23. create index ind_test1_col4 on test1(col4);
    
24. create bitmap index ind_test1_col5 on test1(col5);
    
25. create index ind_test1_col567 on test1(col5 asc,col6 desc,col7 desc);
    
26. create index ind_test1_func on test1(to_number(substr(col8,1,4)||substr(col8,6,2)||substr(col8,9,2)));

复制代码

环境信息如下：
1、oracle参数。
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

2、建测试数据后采集统计信息。
begin
dbms_stats.gather_table_stats(user,'TEST1',estimate_percent=>100,
     method_opt=>'for all indexed columns',cascade=>true);
end;

3、索引统计信息
INDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ---------- ---------- ------------- -----------------
IND_TEST1_COL1_UNQ                      1      74204         74204              1123
IND_TEST1_COL2_REV                      1      74204         74204             74203
IND_TEST1_COL3                          1      74204         74204              1123
IND_TEST1_COL4                          1      74204          9994             73984
IND_TEST1_COL5                          1         37            33                37
IND_TEST1_COL567                        2      74204         73861             51242
IND_TEST1_FUNC                          1      74204            47              1191

4、索引统计信息
SEGMENT_NAME             BLOCKS
-------------------- ----------
IND_TEST1_FUNC              192
IND_TEST1_COL567            576
IND_TEST1_COL5               64
IND_TEST1_COL4              192
IND_TEST1_COL2_REV          192
IND_TEST1_COL3              192
IND_TEST1_COL1_UNQ          192

lanjuuff

1、唯一索引
  高度为2，测试3次，逻辑读均为5次，物理读相差较大。

1. SQL> select col9 from test1 where col1=7001;
   
2. 
   
3. COL9
   
4. --------------------------------------------------
   
5. 6'XB7UHzj!`",%iJEM!@A6@0fEs0>o:B@w\!;}
   
6. 
   
7. Elapsed: 00:00:00.02
   
8. 
   
9. Execution Plan
   
10. ----------------------------------------------------------
    
11. Plan hash value: 3802516197
    
12. 
    
13. --------------------------------------------------------------------------------------------------
    
14. | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    
15. --------------------------------------------------------------------------------------------------
    
16. |   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
    
17. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
    
18. |*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
    
19. --------------------------------------------------------------------------------------------------
    
20. 
    
21. Predicate Information (identified by operation id):
    
22. ---------------------------------------------------
    
23. 
    
24.    2 - access("COL1"=7001)
    
25. 
    
26. 
    
27. Statistics
    
28. ----------------------------------------------------------
    
29.           8  recursive calls
    
30.           0  db block gets
    
31.          [color=Red] 5  consistent gets
    
32.          12  physical reads[/color]
    
33.           0  redo size
    
34.         558  bytes sent via SQL*Net to client
    
35.         523  bytes received via SQL*Net from client
    
36.           2  SQL*Net roundtrips to/from client
    
37.           0  sorts (memory)
    
38.           0  sorts (disk)
    
39.           1  rows processed
    
40. 
    
41. SQL> select col9 from test1 where col1=14001;
    
42. 
    
43. COL9
    
44. --------------------------------------------------
    
45. <<kOl5PtjSJuKuE}?#nLD?<ML>\FuR
    
46. 
    
47. Elapsed: 00:00:00.00
    
48. 
    
49. Execution Plan
    
50. ----------------------------------------------------------
    
51. Plan hash value: 3802516197
    
52. 
    
53. --------------------------------------------------------------------------------------------------
    
54. | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    
55. --------------------------------------------------------------------------------------------------
    
56. |   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
    
57. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
    
58. |*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
    
59. --------------------------------------------------------------------------------------------------
    
60. 
    
61. Predicate Information (identified by operation id):
    
62. ---------------------------------------------------
    
63. 
    
64.    2 - access("COL1"=14001)
    
65. 
    
66. 
    
67. Statistics
    
68. ----------------------------------------------------------
    
69.           8  recursive calls
    
70.           0  db block gets
    
71.          [color=Red] 5  consistent gets
    
72.           1  physical reads[/color]
    
73.           0  redo size
    
74.         550  bytes sent via SQL*Net to client
    
75.         523  bytes received via SQL*Net from client
    
76.           2  SQL*Net roundtrips to/from client
    
77.           0  sorts (memory)
    
78.           0  sorts (disk)
    
79.           1  rows processed
    
80. 
    
81. SQL> select col9 from test1 where col1=147001;
    
82. 
    
83. COL9
    
84. --------------------------------------------------
    
85. b)jz+>"3$lfA Kxl>"Z$Q2gF}T3Fl2!$F#<E'5s,|&aqEIx<;
    
86. 
    
87. Elapsed: 00:00:00.01
    
88. 
    
89. Execution Plan
    
90. ----------------------------------------------------------
    
91. Plan hash value: 3802516197
    
92. 
    
93. --------------------------------------------------------------------------------------------------
    
94. | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    
95. --------------------------------------------------------------------------------------------------
    
96. |   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
    
97. |   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
    
98. |*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
    
99. --------------------------------------------------------------------------------------------------
    
100. 
     
101. Predicate Information (identified by operation id):
     
102. ---------------------------------------------------
     
103. 
     
104.    2 - access("COL1"=147001)
     
105. 
     
106. 
     
107. Statistics
     
108. ----------------------------------------------------------
     
109.           8  recursive calls
     
110.           0  db block gets
     
111.           [color=Red]5  consistent gets
     
112.           2  physical reads[/color]
     
113.           0  redo size
     
114.         569  bytes sent via SQL*Net to client
     
115.         523  bytes received via SQL*Net from client
     
116.           2  SQL*Net roundtrips to/from client
     
117.           0  sorts (memory)
     
118.           0  sorts (disk)
     
119.           1  rows processed

复制代码

lanjuuff

2、反转索引
  高度为2，测试3次，差别较大。
SQL> select col9 from test1 where col2=31;

COL9
--------------------------------------------------
u9w

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1155430086

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL2_REV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL2"=31)


Statistics
----------------------------------------------------------
         70  recursive calls
          0  db block gets
         20  consistent gets
          3  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col2=67;

COL9
--------------------------------------------------
sD6Uspzt/^J8 jB(^K7W9V'U\vM`w f4hcMCc"=*I,G14l+LL

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1155430086

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL2_REV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL2"=67)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        569  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col2=33334;

COL9
--------------------------------------------------
)NW`EW6)bfliR`T_mcc >4z vGm:-g!:;%_rw

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1155430086

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL2_REV |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL2"=33334)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          9  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

3、索引（实际值唯一，但建索时未加unique关键字）
测试3次
SQL> select col9 from test1 where col3=6;

COL9
--------------------------------------------------
rrX=Ul#|)kBi+GT/Z|FbR@T

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1939141208

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("COL3"=6)


Statistics
----------------------------------------------------------
         70  recursive calls
          0  db block gets
         21  consistent gets
         27  physical reads
          0  redo size
        543  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col3=556;

COL9
--------------------------------------------------
)DU{N_T3L#kO?(t[Jt~,veZ}9_c#`Yw3/E*g

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1939141208

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("COL3"=556)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        556  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col3=55556;

COL9
--------------------------------------------------
)NW`EW6)bfliR`T_mcc >4z vGm:-g!:;%_rw

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1939141208

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - access("COL3"=55556)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

3-1加上rownum=1限制
SQL> select col9 from test1 where col3=1001 and rownum=1;

COL9
--------------------------------------------------
Hja!>pdA(5ZuFf4vE(h<:u3E[s^[7AZYFLC+8)]qu9

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 337207461

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    31 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL3"=1001)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col3=9001 and rownum=1;

COL9
--------------------------------------------------
Ldo+)N@"Z#dM=Z2Vp:G

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 337207461

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    31 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL3"=9001)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
         19  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col3=29001 and rownum=1;

COL9
--------------------------------------------------
uIhb

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 337207461

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    31 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    31 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL3 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL3"=29001)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        524  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

4、非唯一索引，+rownum=1
SQL> select col9 from test1 where col4=8888 and rownum=1;

COL9
--------------------------------------------------
]Y 0bRx%7,5d7(s>+vpR&*0JCR_ 5#%r

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3265586927

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     2 |    60 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL4 |     7 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL4"=8888)


Statistics
----------------------------------------------------------
         70  recursive calls
          0  db block gets
         20  consistent gets
          4  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col4=6666 and rownum=1;

COL9
--------------------------------------------------
&/4_Flm-HcCNXrh

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3265586927

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     2 |    60 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL4 |     7 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL4"=6666)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col4=111 and rownum=1;

COL9
--------------------------------------------------
9y(!G UJlMoT'M?_&'AjkfnGI0zXLMO|o(/Q2'[IHdm,

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3265586927

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     2 |    60 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_COL4 |     7 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("COL4"=111)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
         45  physical reads
          0  redo size
        564  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

5、位图索引
SQL> select col9 from test1 where col5='SYS' and rownum=1;

COL9
--------------------------------------------------
8FBZzPrQG{Nxj+B2X*^~_1,0FukuFBG43`8aX;7x+

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 4008074967

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    32 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | TEST1          |     2 |    64 |     4   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | IND_TEST1_COL5 |       |       |            |          |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   4 - access("COL5"='SYS')


Statistics
----------------------------------------------------------
         52  recursive calls
          0  db block gets
         14  consistent gets
         27  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col5='TEST1' and rownum=1;

COL9
--------------------------------------------------
bV "W!v@#lvqv]'Yi

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4008074967

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    32 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | TEST1          |     1 |    32 |     1   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | IND_TEST1_COL5 |       |       |            |          |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   4 - access("COL5"='TEST1')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col5='TEST2' and rownum=1;

COL9
--------------------------------------------------
?|

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4008074967

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    32 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | TEST1          |     1 |    32 |     1   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|                |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | IND_TEST1_COL5 |       |       |            |          |
------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   4 - access("COL5"='TEST2')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
         21  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

6、降序索引--后续再测试
7、函数索引
SQL> select /*+index(ind_test1_func)*/col9 from test1 where to_number(substr(col8,1,4)||substr(col8,6,2)||substr(col8,9,2))=20090815 and rownum=1;

COL9
--------------------------------------------------
2ab\aJ01~tgMlgRt6"jX{k>\e(HdP.yL jH^C^Cu^D

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 2446680474

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    32 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     2 |    64 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_FUNC |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access(TO_NUMBER(SUBSTR("COL8",1,4)||SUBSTR("COL8",6,2)||SUBSTR("COL8",9,2))=200
              90815)


Statistics
----------------------------------------------------------
         68  recursive calls
          0  db block gets
         19  consistent gets
         27  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+index(ind_test1_func)*/col9 from test1 where to_number(substr(col8,1,4)||substr(col8,6,2)||substr(col8,9,2))=20090928 and rownum=1;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2446680474

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    32 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    32 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_FUNC |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access(TO_NUMBER(SUBSTR("COL8",1,4)||SUBSTR("COL8",6,2)||SUBSTR("COL8",9,2))=200
              90928)


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          6  consistent gets
          1  physical reads
          0  redo size
        332  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+index(ind_test1_func)*/col9 from test1 where to_number(substr(col8,1,4)||substr(col8,6,2)||substr(col8,9,2))=20010108 and rownum=1;

COL9
--------------------------------------------------
1K%kTOMB'C5bNge.7|E;~4gHT)f3

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2446680474

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    32 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1          |     1 |    32 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TEST1_FUNC |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access(TO_NUMBER(SUBSTR("COL8",1,4)||SUBSTR("COL8",6,2)||SUBSTR("COL8",9,2))=200
              10108)


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          7  consistent gets
          1  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

lanjuuff

附上测试结果附件。

lanjuuff

结论（可能有误，大神请指正），I/O若无特别说明指逻辑I/O：
1、数据库一次物理读多个块儿。
2、唯一索引比非唯一索引I/O次数少，即使查询结果只有1条记录，一般少一次。
3、非唯一索引where条件中添加rownum=1，则I/O会少一次。
4、第一次查询，可能会存在recursive calls导致I/O上升。
5、函数索引比唯一索引I/O次数多。
还有很多疑问，又以唯一索引为例做测试，发现：I/O一般都是5次，第二次使用完全相同sql则会减为3次；更改查询sql（结果不变，比如更改大小写等），I/O又会变为5次；若查询结果为0行，则I/O为4次（这个可以理解，应该为少了次数据块读。）。
我的理解：索引高度为2，读索引需要2次I/O，读数据块需要1次I/O。莫非解析sql也会产生2次逻辑读？
附上验证过程及附件：

SQL> select col9 from test1 where col1=36;

COL9
--------------------------------------------------
"3gK>r@uZlI2\X=

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=36)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col1=36;

COL9
--------------------------------------------------
"3gK>r@uZlI2\X=

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=36)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where col1=36;

COL9
--------------------------------------------------
"3gK>r@uZlI2\X=

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=36)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where COL1=36;

COL9
--------------------------------------------------
"3gK>r@uZlI2\X=

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=36)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select col9 from test1 where COL1=36;

COL9
--------------------------------------------------
"3gK>r@uZlI2\X=

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=36)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select col9 from test1 where col1=35;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=35)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        332  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select col9 from test1 where col1=35;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3802516197

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1              |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IND_TEST1_COL1_UNQ |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   2 - access("COL1"=35)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        332  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed