Hello gurus,
Please help me understand how function based indexes works?
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS257
taking example from oracle docs,
CREATE OR REPLACE FUNCTION initials (
name IN VARCHAR2
) RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
RETURN('A. J.');
END;
/
Suppose i create the index as
CREATE INDEX func_substr_index ON
EMPLOYEES(SUBSTR(initials(FIRST_NAME),1,4));
First time i use the query as
1. SELECT SUBSTR(initials(FIRST_NAME),1,4) FROM EMPLOYEES WHERE SUBSTR(initials(FIRST_NAME),1,4) = 'A. J.' ;
This will use the index
But if i change the query to
-
SELECT initials(SUBSTR(FIRST\_NAME,1,4)) FROM EMPLOYEES WHERE initials(SUBSTR(FIRST\_NAME,1,4))\= 'A. J.' ;
**Will the second query use the function based index ? My question is will the reversal of function expression cause the index to be used or same order in which index is created** **need to used in query .**
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One more example from oracle docs
Since function based index p recomputes the function value and then indexes those values to the tables
using this query will cause the index idx to be used
1. SELECT a FROM Fbi\_tab WHERE a+b\*(c-1) \< 100;
but if i change the expression to
-
SELECT a FROM Fbi_tab WHERE a+b < 100; OR
SELECT a FROM Fbi_tab WHERE Fx( a,b,c ) < 100; Fx can be UDF or any other expression like (a+b)*c-1
will the index be used ?
Since function based index precomputes the function values and store it in index values to the tables.
If i use any other function expression other than one created in function based index which evaluates to those values calculated in function based index entries, will oracle will rewrite the query to use the function based index ?