What is a Function based Index ?
A Function based Index allows you to have case insenstive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them. It can be used to speed up existing applications without changing any of their logic or queries and can supply additional functionality to applications with very little cost.
Advantages of Function-Based Indexes
How to enable Function Based Indexes
To use function
based indexes the following needs to be done :
-
The system privelege query rewrite to
create function based indexes on tables in your own schema.
-
The system privelege global query rewrite
to create function based indexes on tables in other schemas
-
Function based indexes are only
visible to the Cost Based Optimizer and will not be used by the Rule Based
Optimizer ever.
The following
session or system variables must be set for the optimizer to use function based indexes
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
The meaning of query_rewrite_enabled
is to allow the optimizer to rewrite the query allowing it to use the function based
index. The meaning of is to tell the optimizer to «trust» that the code
marked deterministic by the programmer is in fact deterministic. If the code is in
fact not deterministic (that is, it returns different output given the same inputs),
the resulting rows from the index may be incorrect.
Once the above list has been satisfied, it is as easy as
«CREATE INDEX» from there on in. The optimizer will find and use your
indexes at runtime for you.
Enforcing Unique constraints
Case I
Function based constraints can be used to enforce certain unique constraints which may be difficult otherwise. Suppose you want to make sure that all employee names in emp tables should be unique irrespective of the case. Creating the following index will be a solution.
create unique index emp_name_idx on emp(upper(ename));
Case II
If the requirement is to enforce uniqueness for employee names in a particular department only. Same name can be shared by another department.
create unique index emp_name_idx on emp(deptno, upper(ename));
Case III
Each department should have only one Head of department. ie in each department the designation HOD has to be unique.
create unique index emp_name_idx on emp(deptno, case when designation='HOD' then 0 else empno end);
Enforcing constraints through another table
Some times it may be required to customize the constraints such that only few rows should be considered for constraints enforcement.
The solution is to create another table and a using trigger insert records into this table.
The constraints on this table will also be applicable for the master table through the trigger.
The triiger on EMPLOYEES table will insert rows into emp_name_table
SQL> create table emp_name_table(
2 fname varchar2(30),
3 lname varchar2(30),
4 constraint emp_name_table_pk primary key(fname,lname))
5 ORGANIZATION INDEX;
Table created.
SQL>
SQL> create or replace trigger employees_trg
2 before insert on employees for each row
3 begin
4 insert into emp_name_table values(:new.first_name, :new.last_name);
5 end;
6 /
Trigger created.
SQL>
SQL>
SQL> insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID)
2 values(1001, 'Scott', 'T',sysdate, 1);
1 row created.
SQL> /
insert into employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.EMP_NAME_TABLE_PK) violated
ORA-06512: at "SCOTT.EMPLOYEES_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.EMPLOYEES_TRG'
|