大家都知道:Full table scan 和 Index fast full scan会用到多块读,下面就依据统计信息来确定这点;

physical read total multi block requests:

Total number of Oracle instance read requests which read in two or more database blocks per request for all instance activity including application, backup and recovery, and other utilities

SQL> create table test as select * from all_objects ;

Table created.

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 13

SQL> select count(*) from test;

  COUNT(*)
----------
     67711

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 21

SQL> set autot on explain;
SQL> select count(*) from test;

  COUNT(*)
----------
     67711

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   279   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 67192 |   279   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL>

Full table scan 导致多块读请求增加了8

SQL> create index test_idx_01 on test(object_id);

Index created.

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 21

SQL> select count(*) from test;

  COUNT(*)
----------
     67711

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 22

SQL> set autot on explain ;
SQL> select count(*) from test;

  COUNT(*)
----------
     67711

Execution Plan
----------------------------------------------------------
Plan hash value: 2827309063

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |    46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_IDX_01 | 67192 |    46   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

SQL>

Index fast full scan导致多块读请求增加了1

SQL> create index test_idx_02 on test(object_type);

Index created.

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 22

SQL> select count(*) from test where object_type='TABLE';

  COUNT(*)
----------
      2622

SQL> select b.name,a.value
  2  from v$mystat a
  3      ,v$statname b
  4  where a.statistic# = b.STATISTIC#
  5    and b.NAME = 'physical read total multi block requests';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical read total multi block requests                                 22

SQL> set autot on explain;
SQL> select count(*) from test where object_type='TABLE';

  COUNT(*)
----------
      2622

Execution Plan
----------------------------------------------------------
Plan hash value: 2071593684

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    11 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    11 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_IDX_02 |  2787 | 30657 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='TABLE')

Note
-----
   - dynamic sampling used for this statement

SQL>

Index range scan没有发出多块读请求;