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 |
Comments
Post a Comment