Oracle全表扫描及其执行计划(full table scan)(二)
|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------
scott@ORA11G> set autot off;
scott@ORA11G> alter index t_pk rebuild; -->重建索引
Index altered.
scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: t
Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
-------------- ---------------- -------------------- ------ -------- --------------- ----
T T_PK N 1 VALID NORMAL ASC
c、返回了整个表的大部分数据使用了全表扫描
scott@ORA11G> select count(pad) from t where n<=990;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
|* 2 | TABLE ACCESS FULL| T | 991 | 101K| 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"<=990)
--返回小部分数据时，使用的是索引扫描
scott@ORA11G> select count(pad) from t where n<=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 105 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 1050 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 10 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N"<=10)
d、使用并行方式访问表时使用了全表扫描
scott@ORA11G> select /*+ parallel(3) */ count(pad) from t where n<=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 | 3 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | 105 | | |