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.


Oracle Tuning Tip#15: Index Full Scan


TOPIC:
Index Full Scan

DEFINITION:
Index Full Scan is one of the index access methods supported by Oracle.

Index Full Scan is otherwise called as Full Index Scan.

Index Full Scan can be imposed by Oracle Optimizer only in some scenarios.

One scenario would be, Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes a full-index scan and quickly reads every block of the index without touching the table itself.

Another scenario would be, though the table rows have to be referred, Oracle would still opt for this Index Full Scan only if the column(for which index is created) is mentioned in Order by clause of the query. Advantage over here is, no explicit sorting needs to be done as rows from the index would already be read in the sorting order.

LITTLE-KNOWN FACTS TO BE REMEMBERED:
  • ·         Index Full Scan is totally different from Fast Full Index Scan and the latter will be explained in the next mail.
  • ·         The first scenario I explained earlier wherein, if all the columns mentioned in SELECT, WHERE, GROUP BY, HAVING & ORDER BY clauses are part of the index, then Oracle would prefer to opt for Index full scan instead of full table scan. So with this scan, it completely avoids the table scan.
  • ·         The second scenario I explained earlier wherein if Oracle opts for Index full scan and then refers the table rows, the you should see this step “SORT ORDER BY (NO SORT)” in the explain plan which confirms that Oracle doesn’t put any efforts for sorting since the data already comes in a sorted order from the index table.
ADVANTAGE:
  • ·         Usage of the temporary tablespace will be drastically reduced with this type of scan. As you aware, if oracle opts for this scan, then the data which is being read from index table would already be in the sorted order. 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.
DISADVANTAGE:
  • ·         Index Full Scan can be achieved only by reading block-by-block in the index table since data has to come out in the sorted order. So, it can read single index block at a time.
  • ·         It can’t read the multi index blocks at a time which is a performance hit but the same multi index blocks read is achievable in that other index scan, Fast Full Index Scan.
HOW TO VERIFY:
How to verify whether Oracle follows index 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 (FULL SCAN)                     | SEX_DEPT_INDX                |            |

(Or)

--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         |    Rows    |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |            |
|   1 | SORT ORDER BY (NO SORT)                 |                              |            |
|   2 |  TABLE ACCESS (BY INDEX ROWID)          | EMP                          |            |
|   3 |   INDEX (FULL SCAN)                     | SEX_DEPT_INDX                |            |

For the 1st scenario I explained earlier, you will find a similar explain plan to that of 1st one. Since it doesn’t refer the table, you don’t find the step, “TABLE ACCESS BY INDEX ROWID” in the explain plan.
For the 2nd scenario I explained earlier, you will find a similar explain plan to that of 2nd one. Since it refers the table, you find this step, “TABLE ACCESS BY INDEX ROWID” here. In addition to that, you also find the other step,  “SORT ORDER BY (NO SORT)” which means oracle doesn’t need to do any explicitly sorting since the data which is being read from the index table (before hitting the table rows) is already coming in a sorted order.

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

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

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

Fire this query against this table where the requirement is to display the all the departments where a female employee works in the sorted order by deptid,
Select deptid from emp where sex = ‘F’ order by deptid;

Here, we are yet to create index. Now, to execute this query, Oracle will take 15 seconds.
First 10 seconds will be taken to retrieve all the table record by assuming that Oracle takes 1 second for single data table record retrieval.
Why it is taking another 5 seconds? Reason is, Oracle has to sort all the deptids (since this column is mentioned in the select clause) where a female employee works. We have 5 table records where the sex is ‘F’. So, Oracle will consume these 5 seconds for this sorting purpose. Explain plan will look like this,

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name                                             | Rows  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                                                         |      5       |
|   1 |  TABLE ACCESS FULL                            | EMP                                                |     10      |

Problem here is, oracle has unnecessarily referred the unwanted 5 table records which are not meant to be referred for this sql and the explicit sorting is also done by Oracle. In order to overcome this issue, we have to create a composite index on deptid & sex columns.

Why are deptid and sex columns the ideal candidate to create the composite index and to impose index full scan?
Because if you closely watch this query, the distinct columns used here are only sex & 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 and even explicit sorting is also not required if index full scan is opted.

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

INDEX TABLE:
INDEX VALUE
ROWID
DEPTID
SEX
ADMIN
F
AAAAA3
ADMIN
M
AAAAA6
DELIVERY
F
AAAAA9
DELIVERY
M
AAAAA2
FINANCE
F
AAAAA5
FINANCE
M
AAAAA10
HR
F
AAAAA8
HR
M
AAAAA1
SALES
F
AAAAA4
SALES
M
AAAAA7

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

After creating this index table, fire the same query again,
Select deptid from emp where sex = ‘F’ order by deptid;

When oracle executes this sql, first it looks for any index which has already been created on this “deptid & sex” columns alone. It comes to know that “sex_dept_indx” index has already been created on these columns so it refers this index. To filter the unwanted records, Oracle has to execute WHERE clause of the query. Since, we mention ‘F’ as sex, and this column is not the leading column in the index, Oracle has to go for the “full index scan” means refer all the records in the index table before the filtration. After traversing through all the index records in the index table, Oracle filters the unwanted 5 records and then retrieves other 5 required index records (ADMIN,DELIVERY,FINANCE,HR,SALES) alone in the same order as appear in the index table.

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

Since this index table is used and index full scan is opted by Oracle, oracle will take only 2 seconds (assume 1 second to traverse through all the index records + 1 second to retrieve only those 5 required index records in the same order as appear in the index table so that explicit sorting is not required)

Reason for this scan to be called as “Index 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. After that, it filters 5 unwanted records and only fetches the required 5 index records alone. 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.

Output for this query will be like this as expected,
DEPTID
ADMIN
DELIVERY
FINANCE
HR
SALES

EXAMPLE#2:

For this example also, pls refer the same table structure, data and the index as of previous example.

Fire this query against this table where the requirement is to display the all the employee ids & departments where a female employee works in the sorted order of department id,
Select empid,deptid from emp where sex = ‘F’ order by deptid;

Without the index, Oracle will take same 15 seconds(as explained in the previous example) to display the required output.

After creating this index table(refer this index table in the previous example), fire the same query again,
Select empid,deptid from emp where sex = ‘F’ order by deptid;

When oracle executes this sql, first it looks for any index which has already been created on this “sex” column since this is the filtering criteria here. It comes to know that “sex_dept_indx” index has already been created on this column so it refers this index. To filter the unwanted records, Oracle has to execute WHERE clause of the query. Since, we have mentioned ‘F’ as sex, and this column is not the leading column in the index, Oracle has to go for the “full index scan” means refer all the records in the index table before the filtration. After traversing through all the index records in the index table, Oracle filters the unwanted 5 records and gets the rowids of the required 5 records (AAAAA3, AAAAA9, AAAAA5, AAAAA8 & AAAAA4) alone in the same order as appear in the index table.

After the getting the required ROWIDs, oracle directly refers 3rd,9th,5th,8th & 4th records of the data table since it knows the exact location. With this, it doesn’t refer the remaining 5 table records. Now, explain table will look like this,
--------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         |    Rows    |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    5       |
|   1 | SORT ORDER BY (NO SORT)                 |                              |    5       |
|   2 |   TABLE ACCESS (BY INDEX ROWID)         | EMP                          |    5       |
|   3 |   INDEX (FULL SCAN)                     | SEX_DEPT_INDX                |    10      |

Since this index table is used and index full scan is opted by Oracle, oracle will take only 6 seconds (assume 1 second to get the ROWID of all the required 5 records from the index table in the same order+ 1 second to retrieve the actual table data records since it knows the exact ROWIDs those are retrieved in the previous step each)

Reason for this scan to be called as “Index 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. After that, it filters 5 unwanted records and only fetches the ROWID for the required 5 records alone and only for these 5 records, it hits the table and get the corresponding empid. In this scenario, Oracle must hit the table since empid column is mentioned in the SELECT clause of the query and the empid column is not part of that created index, “sex_dept_indx”. You also find “SORT ORDER BY (NO SORT) in the explain plan since no sorting is explicitly done by Oracle as data is being retrieved from the index table in the sorted order itself.

Output for this query will be like this as expected,
EMPID
DEPTID
3
ADMIN
9
DELIVERY
5
FINANCE
8
HR
4
SALES