通过案例学调优之--Index FULL SCAN和Index FAST FULL SCAN

通过案例学调优之--IndexFULLSCAN和IndexFASTFULLSCANIndexFULLSCAN和ndexFASTFULLSCAN工作原理：IndexFULLSCAN和IndexFASTFULLSCAN的适用情况：适用于我们想选

    index fast full scan：
hint写法：index_ffs(表名 索引名)
原理：从段头开始，读取包含位图块，root block，所有的branch block，leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，每次读取db_file_multiblock_read_count个块。查询某个表记录总数的时候，往往基于primary key的index fast full scan是最有效的。

Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

Full Table Scans : 
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement’s WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

案例分析：

1、创建表和索引

16:02:10 SYS@ prod >create table t as select * from dba_objects where 1=2; Table created. 16:05:43 SYS@ prod >insert into t select * from dba_objects where object_id is not null; 73025 rows created. 16:06:46 SYS@ prod >select count(*) from t;   COUNT(*) ----------      73025       16:06:56 SYS@ prod >commit; Commit complete. 16:13:48 SYS@ prod >exec dbms_stats.gather_table_stats('SYS','T',cascade=>true); PL/SQL procedure successfully completed. 16:14:33 SYS@ prod >set autotrace trace 16:15:32 SYS@ prod >select object_id from t; 73025 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      | 73025 |   356K|   284   (1)| 00:00:04 | |   1 |  TABLE ACCESS FULL| T    | 73025 |   356K|   284   (1)| 00:00:04 | -------------------------------------------------------------------------- Statistics ----------------------------------------------------------         141  recursive calls           0  db block gets        5857  consistent gets        1038  physical reads           0  redo size     1060958  bytes sent via SQL*Net to client       53963  bytes received via SQL*Net from client        4870  SQL*Net roundtrips to/from client           4  sorts (memory)           0  sorts (disk)       73025  rows processed   从上面的执行计划中可知，此时走了全表扫描。   --由于我们需要查询的列为object_id，因此理论上只需要读取索引就应该可以返回所有数据，而此时为什么是全表扫描呢？   --这是因为NULL值与索引的特性所决定的。即null值不会被存储到B树索引。因此应该为表 t 的列 object_id 添加 not null 约束。         16:16:14 SYS@ prod >desc t;  Name                                                              Null?    Type  ----------------------------------------------------------------- -------- --------------------------------------------  OWNER                                                                      VARCHAR2(30)  OBJECT_NAME                                                                VARCHAR2(128)  SUBOBJECT_NAME                                                             VARCHAR2(30)  OBJECT_ID                                                                  NUMBER  DATA_OBJECT_ID                                                             NUMBER  OBJECT_TYPE                                                                VARCHAR2(19)  CREATED                                                                    DATE  LAST_DDL_TIME                                                              DATE  TIMESTAMP                                                                  VARCHAR2(19)  STATUS                                                                     VARCHAR2(7)  TEMPORARY                                                                  VARCHAR2(1)  GENERATED                                                                  VARCHAR2(1)  SECONDARY                                                                  VARCHAR2(1)  NAMESPACE                                                                  NUMBER  EDITION_NAME                                                               VARCHAR2(30)    在object_id上添加not null约束 16:16:42 SYS@ prod >alter table t modify(object_id not null); Table altered. Elapsed: 00:00:00.34 16:16:46 SYS@ prod >desc t  Name                                                              Null?    Type  ----------------------------------------------------------------- -------- --------------------------------------------  OWNER                                                                      VARCHAR2(30)  OBJECT_NAME                                                                VARCHAR2(128)  SUBOBJECT_NAME                                                             VARCHAR2(30)  OBJECT_ID                                                         NOT NULL NUMBER  DATA_OBJECT_ID                                                             NUMBER  OBJECT_TYPE                                                                VARCHAR2(19)  CREATED                                                                    DATE  LAST_DDL_TIME                                                              DATE  TIMESTAMP                                                                  VARCHAR2(19)  STATUS                                                                     VARCHAR2(7)  TEMPORARY                                                                  VARCHAR2(1)  GENERATED                                                                  VARCHAR2(1)  SECONDARY                                                                  VARCHAR2(1)  NAMESPACE                                                                  NUMBER  EDITION_NAME                                                               VARCHAR2(30)

2、对Index_FS和Index_FFS对比