通过案例学调优之--IndexFULLSCAN和IndexFASTFULLSCAN

通过案例学调优之--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对比