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 user defined 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 = 'TOTAL_SAL_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 these global parameters, QUERY_REWRITE_ENABLED to TRUE and QUERY_REWRITE_INTEGRITY to TRUSTED else this index can’t be referred while executing the query. The default value of QUERY_REWRITE_INTEGRITY is ENFORCED and only FBI (on in-built functions) can be invoked with this default value. If FBI (on user defined functions) has to be used by oracle, then QUERY_REWRITE_INTEGRITY must be set to TRUSTED (It means Oracle trusts user defined logic)
· User defined function (on which this FBI index is going to be created) must use this keyword ‘DETERMINISTIC’ else FBI index can’t be created on this function.
· Recompilation of user defined function (on which this FBI index is created) automatically put this FBI index to the DISABLED state.
· Building of Index table will take additional time since index value is a derived value by applying the user defined function on the table data.
ADVANTAGE:
· FBI drastically reduces the execution time of a query if it contains complex calculations in WHERE clause because it is going to be addressed by this FBI index.
DISADVANTAGE:
· Since building of index table is going to take considerable amount of time, execution time of DML operations will get affected
EXAMPLE:
Create an employee table and inserts 10 records. The table will look like this,
DATA TABLE:
ROWID | empid | Column2 | Salary | Bonus | column5 |
AAAAA1 | 1 | .. | 1000 | 35 | .. |
AAAAA2 | 2 | .. | 1080 | 5 | .. |
AAAAA3 | 3 | .. | 1070 | 5 | .. |
AAAAA4 | 4 | .. | 1020 | 25 | .. |
AAAAA5 | 5 | .. | 1040 | 40 | .. |
AAAAA6 | 6 | .. | 1090 | 0 | .. |
AAAAA7 | 7 | .. | 1010 | 60 | .. |
AAAAA8 | 8 | .. | 1060 | 35 | .. |
AAAAA9 | 9 | .. | 1050 | 5 | .. |
AAAAA10 | 10 | .. | 1030 | 35 | .. |
Fire this query against this table where the requirement is to display the all the attributes of an employee whose total salary is “1080”
Select * from emp where salary + bonus = 1080;
First, create normal b*tree non-unique index on both the columns (salary, bonus). Now, to execute this query, Oracle will take 20 seconds (10 seconds to calculate the total salary for all the employees 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 are doing the arithmetical calculation on salary and bonus columns 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 calculate the total salary for all the employees. 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 calculate the total salary (salary + bonus) for all the employees well in advance before the query is fired. This is achieved by creating FBI.
Before creating FBI, first create that user defined function,
User Defined Function:
CREATE OR REPLACE FUNCTION fn_calculate_total_sal(pi_empid Number)
RETURN NUMBER
DETERMINISTIC
DETERMINISTIC
V_totalsal number;
AS
BEGIN
SELECT salary + bonus
AS
BEGIN
SELECT salary + bonus
INTO v_totalsal
FROM employee
FROM employee
WHERE empid = pi_empid;
RETURN v_totalsal;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
Now, Create FBI index on this table for empid column. (create index total_sal_indx on emp(fn_calculate_total_sal(empid))).
Index table will logically look like this,
FBI INDEX TABLE:
INDEX | ROWID |
1035 | AAAAA1 |
1045 | AAAAA5 |
1055 | AAAAA9 |
1065 | AAAAA10 |
1070 | AAAAA7 |
1075 | AAAAA3 |
1080 | AAAAA5 |
1085 | AAAAA2 |
1090 | AAAAA6 |
1095 | AAAAA8 |
First column (INDEX) : it stores all the values of total salary in the ascending order after calculating the same by imposing fn_calculate_total_sal.
Second column (ROWID) : it stores the ROWID of the corresponding record.
After creating this index table, fire the same query with this user defined function,
Select * from emp where fn_calculate_total_sal(empid) = 1080;
When oracle executes this sql, first it looks for any index which has already been created on this “fn_calculate_total_sal” function. It comes to know that the index, “total_sal_indx” has already been created. So, oracle refers the index table first before hitting the actual data table. Since we are looking for the total salary:”1080”, oracle hits index table first and get the corresponding ROWID (AAAAA5). 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 7th index record directly in the index table. So, it won’t touch the remaining 9 index records.
After the getting the required ROWID (AAAAA5), oracle directly refers the 5th record of the data table since it knows the exact location (ROWID of 5th record is AAAAA5). 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) | TOTAL_SAL_INDX | 1 | |
Since this index table is used, oracle will take only 2 seconds (assume 1 second to get the ROWID from 7th 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). Here total salary for all the employees are calculated well in advance (with the help of “fn_calculate_total_sal”) and only this total salary is stored as the index value in index table. So, when query is fired, total salary for the employees is not going to be calculated at run time since it is already available in the index table.
Comments
Post a Comment