Index full scan vs index fast full scan

Index full scan and index fast full scan look similar to each other. They are both common and personalized. The commonality between the two is that scanning is not required.
You can directly return all the required data through the index. This is undoubtedly one of the rare data access methods to improve query performance, because the data stored in the index is usually
Is much smaller than the original table data. Next, let's take a look at the similarities and differences between the two.

 

I. When is index full scan or index fast full scan?
1. All columns in the select and where clauses must have Indexes
2. The total number of data rows returned by the query accounts for more than 10% of the total index. Depends on the value of db_file_multiblock_read_count and the value of parallelism.
3. meet certain criteria such as the number of statistical rows, such as Count. Count (*) Operations almost always use index fast full scan
4. Index full scan is almost always used for operations such as order by on the index column.

Note: The db_file_multiblock_read_count parameter takes effect in the same way as full table scan in index fast full scan because the entire index is accessed.
Multiblock_read is allowed ). Db_file_multiblock_read_count and paralle are only applicable to index fast full scan. Next, paralle
To be confirmed. Once the preceding conditions are met, the cost-based optimizer calls index full scan or index fast full scan Based on the table and index statistics.
You can use the prompt index_ffs to implement index fast full scan.
 
Ii. What is index full scan and index fast full scan?

--> Create a demo table t <br/> Scott @ cnmmbo> Create Table T as select * From dba_objects where 1 = 2; </P> <p> --> fill data with table t <br/> Scott @ cnmmbo> insert into T select * From dba_objects where object_id is not null; </P> <p> Scott @ cnmmbo> commit; </P> <p> -- add an index to the object_id column of Table T <br/> Scott @ cnmmbo> Create index I _t_object_id on T (object_id ); </P> <p> --> collect statistics on table t <br/> Scott @ cnmmbo> exec dbms_stats.gather_table_stats ('Scott ', 't', cascade => True); </P> <p> Scott @ cnmmbo> set autot trace exp; </P> <p> Scott @ cnmmbo> select object_id from T; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 1601196873 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ------------------------------------ ------------------------------------ <Br/> | 0 | SELECT statement | 50422 | 246k | 166 (1) | 00:00:02 | <br/> | 1 | table access full | T | 50422 | 246k | 166 (1) | 00:00:02 | <br/> ------------------------------------------------------------------------ </P> <p> -- the entire table is scanned according to the preceding execution plan. <Br/> -- since the column we want to query is object_id, theoretically we only need to read the index to return all data. Why is full table scan? <Br/> -- this is determined by the null value and the index feature. That is, the null value is not stored in the B-tree index. Therefore, the not null constraint should be added for the object_id column of Table T. <Br/> for null value and index, see <br/> null value and index (1) http://blog.csdn.net/robinson_0612/article/details/7437561 <br/> null value and index (2) http://blog.csdn.net/robinson_0612/article/details/7438397 </P> <p> -- add not null constraints to the column object_id <br/> Scott @ cnmmbo> alter table t modify (object_id not null ); </P> <p> -- execution plan after constraints are added <br/> Scott @ cnmmbo> select object_id from T; </P> <p> execution plan <br/> ------------------------------------------ ---------------- <Br/> plan hash value: 2036340805 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> hour <br/> | 0 | SELECT statement | 50422 | 246k | 26 (0) | 00:00:01 | <br/> | 1 | index fast full scan | I _ T_object_id | 50422 | 246k | 26 (0) | 00:00:01 | <br/> average </P> <p> -- as shown in the preceding execution plan, in this case, the index is quickly scanned, and the entire cost is an order of magnitude lower than the full table scan </P> <p> index fast full scan <br/> is similar to full table scan, when no required index block is found in the cache, multiple read operations are performed based on the value of db_file_multiblock_read_count <br/>. The branch structure of the index is simply obtained, and then all leaf nodes are scanned. The result is that the index structure is not accessed, and the obtained data is not sorted according to the order of the index key. <Br/> index fast full scan uses multiblock_read, so the DB file scattered reads event is generated. </P> <p> -- can I use full index scan in the above case? The answer is yes. You need to add a prompt <br/> Scott @ cnmmbo> select/* + index (T I _t_object_id) */object_id from T; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 431110666 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> ---------------------------------- -------------------------------------------- <Br/> | 0 | SELECT statement | 50422 | 246k | 113 (1) | 00:00:02 | <br/> | 1 | index full scan | I _t_object_id | 50422 | 246k | 113 (1) | 00:00:02 | <br/> -------------------------------------------------------------------------------- </P> <p> index full scan <br/> is different from index fast full scan, index full scan accesses the entire index tree in order of index storage. After accessing the leaf node, read the value of the connected node in a two-way <br/> linked list. In other words, all data on the index is read in an orderly manner. If the index block is not found in the cache, you need to read it from the data file. <br/>. This will make full index scans that need to read a large amount of data inefficient. Index full scan uses single read, so the <br/> dB file sequential reads event is generated. The new version of Oracle supports dB file parallel reads.

Iii. Differences between index full scan and index fast full scan

-- We still use the preceding query to add the order by clause for the original statement <br/> Scott @ cnmmbo> set autot trace; <br/> Scott @ cnmmbo> select object_id from t order by object_id; </P> <p> 50422 rows selected. </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 431110666 </P> <p> -------------------------------------------------------------------------------- <br/> | ID | operation | Name | row S | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 50422 | 246k | 113 (1) | 00:00:02 | <br/> | 1 | index full scan | I _t_object_id | 50422 | 246k | 113 (1) | 00:00:02 | <br/> Statistics </P> <p> Statistics <br/> ------------------------- ------------------------------- <Br/> 1 recursive CILS <br/> 0 dB block gets <br/> 3467 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/>/> 918087 bytes sent via SQL * Net to client <br/> 37463 bytes encoded ed via SQL * Net from client <br/> 3363 SQL * Net roundtrips to/from client <br/> 0 sorts (memory) <br/> 0 sorts (Disk) <br/> 50422 rows processed </P> <p> -- when the order by clause is added, we find that no prompt is added. The CBO optimizer selects the index full scan mode for scanning <br/> -- according to the above statistics, the consistent gets is 3467, And the sorts (memory) 0 </P> <p> -- The following shows how to perform index fast full scan <br/> Scott @ cnmmbo> select/* + index_ffs (T I _t_object_id) */object_id from t order by object_id; </P> <p> 50422 rows selected. </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 2527678987 </P> <p> ----------------- ---------------------------------------------------------------------------- <Br/> | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 50422 | 246k | 185 (3) | 00:00:03 | <br/> | 1 | sort order by | 50422 | 246k | 1208k | 185 (3) | 00:00:03 | <br/> | 2 | Index fast full scan | I _t_object_id | 50422 | 246k | 26 (0) | 00:00:01 | <br/> Statistics </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 118 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 918087 bytes sent via S QL * Net to client <br/> 37463 bytes encoded ed via SQL * Net from client <br/> 3363 SQL * Net roundtrips to/from client <br/> 1 sorts (memory) <br/> 0 sorts (Disk) <br/> 50422 rows processed </P> <p> -- indicates that the execution plan takes effect, scan by index fast full scan <br/> -- note that step 1 in the execution plan is the sort order by operation, but not the index full scan operation. The execution plan contains an extra tempspc column with a value of 1208 k <br/> -- at this time, the consistent gets is 118, which is an order of magnitude lower than 3467. Next we can see sorts (memory) and the sorts (memory) value in the previous step is 0. </P> <p> -- use the following prompt to enable the full table scan method to observe its Statistical Information <br/> Scott @ cnmmbo> select/* + full (t) */object_id from t order by object_id; </P> <p> 50422 rows selected. </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 961378228 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | tempspc | Co ST (% CPU) | time | <br/> average <br/> | 0 | SELECT statement | 50422 | 246k | 324 (2) | 00:00:04 | <br/> | 1 | sort order by | 50422 | 246k | 1208k | 324 (2) | 00:00:04 | <br/> | 2 | table access full | T | 50422 | 246k | 166 (1) | 00:00:02 | <br/> average -------------------------------------------------------------------------------- --- </P> <p> Statistics <br/> 1 recursive CILS <br/> 0 dB block gets <br/> 726 consistent gets <br/> 0 physical reads <br/> 0 redo size <br/> 918087 bytes sent via SQL * Net to client <br/> 37463 bytes encoded ed via SQL * Net from client <br/> 3363 SQL * Net roundtrips to/from client <br/> 1 sorts (memory) <br/> 0 sorts (Disk) <br/> 50422 rows Processed </P> <p> -- read data in full table mode in the execution plan <br/> -- full table scan is equivalent to index fast full scan in step 2, the second step in the execution plan has the sort order by operation. And column tempspc, value: 1208 k <br/> -- the consistent gets of this full table scan is 726, higher than the consistent gets of index fast full scan, consistent gets lower than index full scan </P> <p> -- the following uses descending order <br/> Scott @ cnmmbo> set autot trace exp; <br/> Scott @ cnmmbo> select object_id from t order by 1 DESC; </P> <p> execution plan <br/> explain <br/> plan hash value: 2808014233 </P> <p> -------------------------------- ---------------------------------------------------------- <Br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 50422 | 246k | 113 (1) | 00:00:02 | <br/> | 1 | index full scan descending | I _t_object_id | 50422 | 246k | 113 (1) | 00:00:02 | <br/> --------------- --------------------------------------------------------------------------- </P> <p> -- as shown in the preceding execution plan, Oracle does not hesitate to select index full scan as long as sorting operations are involved, because the index full scan method must be <br/> -- sort by the index creation method. When order by uses a descending order, you can see that the corresponding operation type is the descending order operation index full scan descending.

4. Index full scan and index fast full scan during count (*) Aggregation

Scott @ cnmmbo> set autot trace exp; <br/> Scott @ cnmmbo> select count (*) from T; </P> <p> execution plan <br/> ---------------------------------------------------------- <br/> plan hash value: 3095383276 </P> <p> threads <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> -------------------------------------------------------- ----------------------- <Br/> | 0 | SELECT statement | 1 | 26 (0) | 00:00:01 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | index fast full scan | I _t_object_id | 50422 | 26 (0) | 00:00:01 | <br/> explain </P> <p> Scott @ cnmmbo> select/* + index (T I _t_object_id) */count (*) from T; </P> <p> execution plan <br/> ---------------------- ---------------------------------- <Br/> plan hash value: 3079973526 </P> <p> average <br/> | ID | operation | Name | rows | cost (% CPU) | time | <br/> -------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 1 | 113 (1) | 00:00:02 | <br/> | 1 | sort aggregate | 1 | <br/> | 2 | Index Full scan | I _t_object_id | 50422 | 113 (1) | 00:00:02 | <br/> success <br/> --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612 <br/> Notes for using count and count (*) <br/> if it is based on a column that can be null value for count, the query optimizer selects any index containing the column <br/> if it is based on the not null value column for count, or count (*), at least one non-null column is contained and the smallest index is stored in principle, because the null value is not stored in the B-tree index. </P> <p> -- Appendix: SQL statement for searching index full scan <br/> select P. SQL _id, SQL _text <br/> from V $ sqlarea T, V $ SQL _plan P <br/> where T. hash_value = P. hash_value and P. operation = 'index' and P. options = 'full scan' <br/> and P. object_owner not in ('sys ', 'system ');

5. Index full scan and index fast full scan for Bitmap indexes
When Bitmap indexes are used, index full scan and index fast full have the same performance as B-tree indexes. Note that Bitmap indexes store null values to a large extent.
To compress the index. Secondly, bitmap indexes do not support descending scanning. The index full scan and index fast full of Bitmap indexes are not demonstrated here.

 

Vi. Summary
1. When the columns in select and where both have indexes, It is the prerequisite for index full scan and index fast full scan.
2. The total number of data rows returned by the query accounts for more than 10% of the total index.
3. Based on the preceding premise, the count (*) operation almost always selects index fast full scan, while the order by clause on the index column almost always selects index full scan.
4. Index fast full scan reads index blocks using multiple reads to generate dB file scattered reads events. The read operations are efficient but unordered.
5. Index full scan reads index blocks sequentially in a single read mode, resulting in dB file sequential reads events. When reading a large number of indexes in full scan mode, the efficiency is low.
6. In most cases, index fast full scan has better performance than index full scan. However, when order by exists, the former must have a re-sorting process for read blocks.
7. Index fast full scan sacrifices the memory and temporary tablespace in exchange for performance. Therefore, a reasonable balance should be made between insufficient or saturated memory.

 

VII. More references

Null Value and index (1)

Null Value and index (2)

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor