文章来自:http://www.xifenfei.com/2968.html
SQL> create table test as select object_id,object_name from dba_objects;
create table test as select object_id,object_name from dba_objects;
Table created.
SQL> create index i_t_object_id on test(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats(USER,'TEST',cascade=>true);
PL/SQL procedure successfully completed.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
table access full
SQL> SET AUTOT TRACE EXP STAT
SQL> SELECT OBJECT_ID FROM test;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72516 | 354K| 95 (0)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 72516 | 354K| 95 (0)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
128 recursive calls
0 db block gets
5176 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
72516 rows processed
SQL> select /*+ index(T i_t_object_id) */ object_id from test;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72516 | 354K| 95 (0)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST | 72516 | 354K| 95 (0)| 00:00:02 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5160 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72516 rows processed
从上面的执行计划中可知,此时走了全表扫描. 由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢? 这是因为NULL值与索引的特性所决定的.即null值不会被存储到B树索引.因此应该为表 t_xifenfei 的列 object_id 添加 not null 约束.
INDEX FAST FULL SCAN
SQL> alter table test modify(object_id not null);
Table altered.
SQL> SELECT object_id from test;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 72516 | 354K| 45 (0)| 00:0
0:01 |
| 1 | INDEX FAST FULL SCAN| I_T_OBJECT_ID | 72516 | 354K| 45 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Statistics
----------------------------------------------------------
128 recursive calls
0 db block gets
5010 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
72516 rows processed
INDEX FAST FULL SCAN:当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操作.对于索引的分支结构只是简单的获取,然后扫描所有的叶结点.其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序.INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件
INDEX RANGE SCAN
SQL> select object_id from test where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2197008162
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 8 | 40 | 2 (0)| 00:00:01
|
|* 1 | INDEX RANGE SCAN| I_T_OBJECT_ID | 8 | 40 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
629 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
SQL> select /*+ index(T i_t_object_id) */ object_id from test t;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72516 | 354K| 162 (0)| 00:00:02 |
| 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 72516 | 354K| 162 (0)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4986 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72516 rows processed
INDEX列ORDER BY
SQL> SELECT OBJECT_ID FROM Test order by object_id ;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 431110666
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72516 | 354K| 162 (0)| 00:00:02 |
| 1 | INDEX FULL SCAN | I_T_OBJECT_ID | 72516 | 354K| 162 (0)| 00:00:02 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4986 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72516 rows processed
SQL> SELECT OBJECT_ID FROM Test order by object_id desc;
72516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2808014233
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72516 | 354K| 162 (0)| 00:00:02 |
| 1 | INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 72516 | 354K| 162 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4987 consistent gets
0 physical reads
0 redo size
1324315 bytes sent via SQL*Net to client
53697 bytes received via SQL*Net from client
4836 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72516 rows processed
对于index 列排序,默认情况下会使用INDEX FULL SCAN/INDEX FULL SCAN DESCENDING而不选择使用INDEX FAST FULL SCAN,因为INDEX FAST FULL SCAN获得数据后,还需要做一次SORT ORDER BY操作
SQL> SELECT max(object_id) FROM Test;
Execution Plan
----------------------------------------------------------
Plan hash value: 2939893782
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| I_T_OBJECT_ID | 1 | 5 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
534 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
sort aggregate通常发生在使用一些聚合函数的时候,sum(),avg(),min(),max(),count()等等,实际上sort aggregate不做真正的sort,并不会用到排序空间,而是通过一个全局变量+全表或全索引扫描来实现.这样的操作在默认情况下使用INDEX FAST FULL SCAN