Monday, October 11, 2010

Oracle Tuning Tip#16: Index Fast Full Scan


TOPIC:
Index Fast Full Scan

DEFINITION:
Index Fast Full Scan is one of the index access methods supported by Oracle.
Index Fast Full Scan is otherwise called as Fast Full Index Scan also.
Index Fast Full Scan can be imposed by Oracle Optimizer only in the certain scenario.

The scenario where this index would be invoked is, Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes this fast full index scan and quickly reads every block of the index without touching the table itself provided that query doesn’t contain any ORDER BY clause. The definition for this scan is more or less similar to the index full scan but the only difference is that former  will be invoked when ORDER BY clause is not mentioned in the query. It differs from the index full scan in another way where output won’t be in the sorted order since ORDER BY clause is not mentioned.

If Oracle detects the query is serviceable without touching the table rows, it means, all the columns mentioned in the query(like in SELECT, WHERE clauses) should already be part of an existing index. Since all the data can be retrieved from the index table itself, it doesn’t need to bother about hitting the table.

LITTLE-KNOWN FACTS TO BE REMEMBERED:
  • ·         Index Fast Full Scan can be invoked by this hint, /*+index_ffs(<<index name>>) */
  • ·         Since the data doesn’t need to come in the sorted order, Index Fast Full Scan invokes a kinda of process wherein by reading multiple index blocks at a time thereby improving the query performance drastically.
  • ·         Index Fast Full Scan and Full table scan are same and the only difference is that former refers index table only wherein latter refers data table only.
  • ·         The major difference between fast full index scan and full index scan is that former will be invoked only if ORDER BY clause is not mentioned in the query.
  • ·         Another difference between fast full index scan and full index scan is that table data will never be accessed at any cost if the former is invoked.
  • ·         Since multiple index blocks can be read at a time in this scan, throughput of the query is directly proportional to this global parameter, “db_file_multiblock_read_count”. Generally, this parameter will be set to either 16 or 32. Number of index blocks those can be read at a time will be doubled if this value is changed from 16 to 32.
ADVANTAGE:
  • ·         If a query goes for this index fast full scan, then it doesn’t require the usage of the temporary tablespace since ORDER BY clause is not mentioned in the query. Temporary tablespace is used only when oracle does the sorting on its own. So, Oracle doesn’t require any space in temporary tablespace in this scenario.
  • ·         If index full scan is followed, then we can eliminate full table scan completely thereby we can reduce the execution time of query, number of data blocks to be referred and I/O.
  • ·         Since multi index blocks read is supported by this scan (unlike index full scan where only single index block can be read at a time), query performance would be good and better.
DISADVANTAGE:
  • ·         If you want the output to be in some sorted order, then this scan can’t be invoked.
  • ·         If some columns mentioned in the query are not part of the index, then if you explicitly invokes this scan by the hint, then performance would go for a toss.
HOW TO VERIFY:
How to verify whether Oracle follows index fast full scan or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,
--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         |    Rows    |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |            |
|   1 |   INDEX (FAST FULL SCAN)                | EMP_DEPT_INDX                |            |

In the explain plan, whenever it follows the index fast full scan, it displays the keyword (FAST FULL SCAN) in the operation column against the index name.

EXAMPLE:
Create an employee table and inserts 10 records. The table will look like this,

DATA TABLE:
ROWID
Empid
column2
column3
column4
deptid
AAAAA1
1
..
..
..
HR
AAAAA2
2
..
..
..
DELIVERY
AAAAA3
3
..
..
..
ADMIN
AAAAA4
4
..
..
..
SALES
AAAAA5
5
..
..
..
FINANCE
AAAAA6
6
..
..
..
ADMIN
AAAAA7
7
..
..
..
SALES
AAAAA8
8
..
..
..
HR
AAAAA9
9
..
..
..
DELIVERY
AAAAA10
10
..
..
..
FINANCE

Fire this query against this table where the requirement is to display the all the employee ids along with their departments,
Select empid,deptid from emp;

Why are deptid and sex columns the ideal candidate to create the composite index and to impose fast index full scan?
Because if you closely watch this query, the distinct columns used here are only empid & deptid columns. So if an index is created for these 2 columns, then Oracle doesn’t need to refer the table records at all since all the required data can be retrieved from the index table itself if this full index full scan is opted.

Create a composite index on this table for empid and deptid columns. (create index emp_dept_indx on emp(empid,deptid)).
Index table will logically look like this,

INDEX TABLE:
INDEX VALUE
ROWID
EMPID
DEPTID
1
HR
AAAAA1
2
DELIVERY
AAAAA2
3
ADMIN
AAAAA3
4
SALES
AAAAA4
5
FINANCE
AAAAA5
6
ADMIN
AAAAA6
7
SALES
AAAAA7
8
HR
AAAAA8
9
DELIVERY
AAAAA9
10
FINANCE
AAAAA10

First column (INDEX) : it stores all the unique combination values of empid & deptid columns in the ascending order.
Second column (ROWID) : it stores the ROWID of the corresponding records.

When oracle executes this sql, first it looks for any index which has already been created on these “empid & deptid” columns alone. It comes to know that “emp_dept_indx” index has already been created on these columns so it refers this index. Since there is no WHERE clause in the query, it won’t filter any records. Since, both these two columns are part of the index, Oracle can go for the “fast full index scan” instead of “full index scan” since ORDER BY clause is not mentioned. After traversing through all the index records in the index table, Oracle displays all the index records in the output.

With this, it doesn’t refer the table records at all since all the required columns(empid & deptid) are already available in the index table itself. Now, explain table will look like this,
--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         |    Rows    |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    10      |
|   1 |   INDEX (FAST FULL SCAN)                | EMP_DEPT_INDX                |    10      |

Output for this query may be look like this,
EMPID
DEPTID
4
SALES
10
FINANCE
6
ADMIN
9
DELIVERY
5
FINANCE
2
DELIVERY
1
HR
8
HR
3
ADMIN
7
SALES

Since this index table is only used and fast index full scan is opted by Oracle, oracle will take only 1 second (assume 1 second to traverse through all the index records)

Reason for this scan to be called as “Index Fast Full Scan” is, Oracle has to traverse through all the records in the index table and that’s why you see value as 10 in “Rows” column of the explain plan. This explain tells that the table records are not at all referred since you don’t find “TABLE ACCESS BY INDEX ROWID” in the explain plan. If you closely check the output, you can notice that records in the output is not in the same order as the index table and output records are not in the sorted order(because there is no ORDER BY clause in the query). Since output records are in the jumbled order, we can say that multiple index records (i.e., index blocks) have been read concurrently as well as randomly.


4 comments:

  1. Hi,
    Very well explained! Can you please explain the loops and joins too? like hash joins and nested loops.

    ReplyDelete
  2. Great Explanation. Thanks for sharing your knowledge!!! Really useful !!

    ReplyDelete
  3. Great. It is very easy to understand.

    ReplyDelete