Oracle全表扫描及其执行计划(full table scan)(三)
| | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 105 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 105 | | | Q1,00 | PC
WP | |
| 5 | PX BLOCK ITERATOR | | 10 | 1050 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| T | 10 | 1050 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("N"<=10)
Note
-----
- Degree of Parallelism is 3 because of hint
e、使用full提示时使用了全表扫描
scott@ORA11G> select /*+ full(t) */ count(pad) from t where n<=10;
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 | 10 | 1050 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"<=10)
f、统计信息缺失导致全表扫描的情形
scott@ORA11G> exec dbms_stats.delete_table_stats('SCOTT','T');
PL/SQL procedure successfully completed.
scott@ORA11G> select count(pad) from t where n<=10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS FULL| T | 10 | 650 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("N"<=10)
Note
-----
- dynamic sampling used for this statement (level=2)
--上面的执行计划使用了全表扫描，而且提示使用了动态采样，也就是缺乏统计信息
--表上的数据块小于DB_FILE_MULTIBLOCK_READ_COUNT值的情形可能产生全表扫描的情形不演示
4、全表扫描何时低效
[sql]
--先来做几个实验
a、演示表上的相关信息
scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table
Table Name Index Name CL_NAM CL_POS Status IDX_TYP DSCD
------------------------- ------------------------- --------- ------ -------- --------------- ----
BIG_TABLE BIG_TABLE_PK ID 1 VALID NORMAL ASC
scott@ORA11G> @idx_stat
Enter value for input_table_name: big_table
Enter value