FBI is one of the index types that is supported by oracle. It more or less behaves like other type of b*tree indexes(unique,non-unique,reverse) but only difference is the way it builds the index table. In this, when it builds Index table, index value is derived by applying the in-built function on the table data. So, index data value and actual table data value would be different here. FBI is simply an index that uses the function so that the database can make direct comparisons between the index values and the filter values.
This FBI index can be created on a single column or more than one column. More than one FBI can be created for the same data table but for the different columns.
HOW TO VERIFY:
How to verify whether the created index is FBI or not? Fire this query,
select index_type from user_indexes where index_name = 'EMP_NAME_INDX'
ð This query would return “FUNCTION-BASED NORMAL” (it means it is B*Tree index but of “FBI” type)
LITTLE-KNOWN FACTS TO BE REMEMBERED:
· In order to use this FBI, we have to set this global parameter, QUERY_REWRITE_ENABLED to TRUE else this index can’t be referred while executing the query.
· Building of Index table might take additional time since index value is a derived value by applying the in-built function on the table data.
ADVANTAGE:
· FBI allows you to have case insenstive searches or sorts
· FBI allows to search on complex equations easily
DISADVANTAGE:
· Since building of index table is going to take considerable amount of time, execution time of DML operations might get affected
EXAMPLE:
Create an employee table and inserts 10 records. The table will look like this,
DATA TABLE:
ROWID | empid | empname | column3 | column4 | column5 |
AAAAA1 | 1 | ALEX | .. | .. | .. |
AAAAA2 | 2 | mark | .. | .. | .. |
AAAAA3 | 3 | Matthew | .. | .. | .. |
AAAAA4 | 4 | VikaS | .. | .. | .. |
AAAAA5 | 5 | NADAL | .. | .. | .. |
AAAAA6 | 6 | morgan | .. | .. | .. |
AAAAA7 | 7 | Roger | .. | .. | .. |
AAAAA8 | 8 | george | .. | .. | .. |
AAAAA9 | 9 | JOhn | .. | .. | .. |
AAAAA10 | 10 | MARtin | .. | .. | .. |
Fire this query against this table where the requirement is to display the all the attributes of an employee whose empname is “GEORGE”
Select * from emp where upper(empname) = ‘GEORGE’;
First, create normal b*tree non-unique index on the “empname” column. Now, to execute this query, Oracle will take 20 seconds (10 seconds to convert the value of empname column into uppercase and 10 seconds for entire table data search (assume oracle takes 1 second for single data table record search)). Explain plan will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | |
| 1 | TABLE ACCESS FULL | EMP | 10 | |
Explain plan tells us that the created b*tree non-unique index has not been used by the query and opted for table access full. This is because we have imposed upper() on empname column and that’s why it has gone for the table access full. But why it did take 20 seconds? Before comparing the value, first Oracle has to convert the all the value in empname column into uppercase. So, it has spent 10 seconds there. Then, it has to do the sequential search on this converted data. So, it has spent another 10 seconds for this.
In order to bring down the response time, first we have to make sure that query uses the index table so that random search is entertained. In addition to this, we have to convert the values of empname column into uppercase well in advance before query is fired. This is achieved by creating FBI.
Create FBI index on this table for empname column. (create index emp_name_indx on emp(upper(empname))).
Index table will logically look like this,
FBI INDEX TABLE:
INDEX | ROWID |
ALEX | AAAAA1 |
GEORGE | AAAAA8 |
JOHN | AAAAA9 |
MARK | AAAAA2 |
MARTIN | AAAAA10 |
MATTHEW | AAAAA3 |
MORGAN | AAAAA6 |
NADAL | AAAAA5 |
ROGER | AAAAA7 |
VIKAS | AAAAA4 |
First column (INDEX) : it stores all the values of deptid column in the ascending order after converting to uppercase.
Second column (ROWID) : it stores the ROWID of the corresponding record.
After creating this index table, fire the same query again,
Select * from emp where upper(empname) = ‘GEORGE’;
When oracle executes this sql, first it looks for any index which has already been created on this “empname” column. It comes to know that the index, “emp_name_indx” has already been created on “empname” column. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the empname:’GEORGE’, oracle hits index table first and get the corresponding ROWID (AAAAA8). When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding index record. Using this binary search, Oracle is intelligent enough to go to the 2nd index record directly in the index table. So, it won’t touch the remaining 9 index records.
After the getting the required ROWID (AAAAA8), oracle directly refers the 8th record of the data table since it knows the exact location (ROWID of 8th record is AAAAA8). With this, it doesn’t refer the remaining 9 table records. Now, explain table will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | |
| 1 | TABLE ACCESS (BY INDEX ROWID) | EMP | 1 | |
| 2 | INDEX (RANGE SCAN) | EMP_NAME_INDX | 1 | |
Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID of 2nd index record since it follows binary search + 1 second to retrieve the actual table data record since we know the exact ROWID which is retrieved in the previous step)
Comments
Post a Comment