|* 2 | TABLE ACCESS FULL| BIG_TABLE | 20046 | 352K| 413 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=40000 AND "ID">=20000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1486 consistent gets
1484 physical reads
0 redo size
427 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--注意对比上面两次操作中的consistent gets与physical reads
c、查询返回30%数据行的情形
scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select sum(object_id),avg(object_id) from big_table where id between 20000 and 50000;
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829 --->尽管返回数据的总行数为30%，而此时优化器使用了全表扫描
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 413 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 30012 | 527K| 413 (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=50000 AND "ID">=20000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1486 consistent gets
1484 physical reads
0 redo size
427 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--下面使用提示来强制优化器走索引扫描
scott@ORA11G> alter system flush buffer_cache;
scott@ORA11G> select /*+ index(big_table big_table_pk) */ sum(object_id),avg(object_id)
2 from big_table where id between 20000 and 50000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 511 (1)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 30012 | 527K| 511 (1)| 00:00:07 |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK | 30012 | | 64 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------