Oracle全表扫描及其执行计划(full table scan)(六)
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">=20000 AND "ID"<=50000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
526 consistent gets
526 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
--注意观察每一次测试时所耗用的物理读与逻辑读
--从上面的测试可以看出，当表上所返回的数据行数接近于表上的30%时，
Oracle 倾向于使用全表扫描
--而对于表上所返回的数据行数接近于表上的30%的情形，我们给与索引提示，此时比全表扫描更高效，即全表扫描是低效的
--笔者同时测试了数据返回总行数接近80%的情形以及创建了一个百万记录的进行对比测试
--大致结论，如果查询所返回的数据的总行数仅仅是表上数据的百分之八十以下，而使用了全表扫描，即可认为该全表扫描是低效的
--注：
--具体情况需要具体分析，如果你的表是千万级的，返回总数据的百分之零点几都会导致很大的差异
--其次，表上的索引应具有良好的聚簇因子，如不然，测试的结果可能有天壤之别
--最后，上面所描述的返回总行数应与执行结果返回的行数有差异，是指多少行参与了sum(object_id)
5、小表的全表扫描是否高效
[sql]
--使用scott下dept表，仅有4行数据
scott@ORA11G> select * from dept where deptno>10;
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2985873453 --->执行计划选择了索引扫描
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 60 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_DEPT | 3 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO">10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets -->使用了4次逻辑读
0 physical reads
0 redo size
515 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)
3 rows processed
-->下面强制使用全表扫描
scott@ORA11G> select /*+ full(dept) */ * from dept where deptno>10;
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DEPT | 3 | 60 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -