关注SQL执行计划中的两个比率(二)
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 799K| 74M| 2471 (3)| 00:00:30 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 799K| 74M| 2471 (3)| 00:00:30 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<800000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
63568 consistent gets
2309 physical reads
0 redo size
85856864 bytes sent via SQL*Net to client
587048 bytes received via SQL*Net from client
53335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
799999 rows processed
Oracle CBO根据QUBE，采用TABLE ACCESS FULL的访问路径，代价cost=4942，执行时间为45.74秒
下面强制让CBO走索引
[sql]
hr@ORCL> select /*+ index(t ind_t)*/* from t where rownum<800000;
799999 rows selected.
Elapsed: 00:00:52.48
Execution Plan
----------------------------------------------------------
Plan hash value: 1019664585
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 799K| 74M| 802K (1)| 02:40:32 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 799K| 74M| 802K (1)| 02:40:32 |
| 3 | INDEX FULL SCAN | IND_T | 1611K| | 1790 (2)| 00:00:22 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<800000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
854966 consistent gets
795 physical reads
0 redo size
84677684 bytes sent via SQL*Net to client
587048 bytes received via SQL*Net from client
53335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
799999 rows processed
这次走INDEX FULL SCAN，代价cost=1604k+1790，执行时间是52.48秒.