SAMEER
WADHWA
Introduction : With the help
of the following examples , I tried to explain the function based index,
available in ORACLE RDBMS from Oracle version 8.1.x.
Let us
consider the emp table in scott schema as
ora816 SamSQL :> select * from scott.emp;
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ----------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800 20
7499 ALLEN
SALESMAN 7698
20-FEB-81 1600 300 30
7521 WARD
SALESMAN 7698
22-FEB-81 1250 500 30
7566 JONES
MANAGER 7839
02-APR-81 2975 20
7654 MARTIN
SALESMAN 7698
28-SEP-81 1250 1400 30
7698 BLAKE
MANAGER 7839
01-MAY-81 2850 30
7782 CLARK
MANAGER 7839
09-JUN-81 2450 10
7788 SCOTT
ANALYST 7566
19-APR-87 3000 20
7839 KING
PRESIDENT
17-NOV-81 5000 10
7844 TURNER
SALESMAN 7698
08-SEP-81 1500 0 30
7876 ADAMS
CLERK 7788
23-MAY-87 1100 20
7900 JAMES
CLERK 7698
03-DEC-81 950 30
7902 FORD
ANALYST 7566
03-DEC-81 3000 20
7934 MILLER
CLERK 7782
23-JAN-82 1300 10
14 rows
selected.
Elapsed:
00:00:00.70
ora816 SamSQL :> create index emp_indx_fb1 on scott.emp(job);
Index
created.
Elapsed:
00:00:00.40
ora816 SamSQL :> select /*+ INDEX_ASC (T1 emp_indx_fb1) */ * from
scott.emp T1
2 where upper(job) = 'CLERK';
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ----------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800 20
7876 ADAMS
CLERK 7788
23-MAY-87 1100 20
7900 JAMES
CLERK 7698
03-DEC-81 950 30
7934 MILLER
CLERK 7782
23-JAN-82 1300 10
Elapsed: 00:00:00.41
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1 Card=1 Bytes=87)
1 0
TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=87)
You
will notice above that i am using upper
function in job and hence forcing index to use. but it is not using and full
table scan is going on.
Now the
question arises how do we make a Function Based Index or FBI.
To do
that
Step 0 : You must have an Oracle 8i Enterprise Edition.
Step 1 :- check the following parameters
ora816 SamSQL :> select substr(name,1,40) Name,substr(value,1,40)
value from v$parameter where name like '%query%';
NAME VALUE
----------------------------------------
----------------------------------------<
query_rewrite_enabled FALSE
query_rewrite_integrity enforced
If you
got the result as above. Open you init parameter file and add the following two
lines, shutdown the database and
restart.
Step 2 :-
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
ora816 SamSQL :> select substr(name,1,40) Name,substr(value,1,40)
value from v$parameter where name like '%query%';
NAME VALUE
----------------------------------------
----------------------------------------<
query_rewrite_enabled TRUE
query_rewrite_integrity TRUSTED
If you
run query again you will see above results
Step 3 :-
You
have to connect as sys and give grants
to the specified user ,where you want to create a FBI.
ora816 SamSQL :> grant query rewrite to scott;
Here i
gave grant query rewrite to scott .
Step 4 :-
Now
create a Function Based index .
ora816 SamSQL :> drop index emp_indx_fb1;
Index
dropped.
I
droped above index since i created in the begining.
Elapsed:
00:00:00.20
Example 1:
ora816 SamSQL :> create index emp_indx_fb1 on
scott.emp(upper(job)) compute statistics;
Index
created.
Elapsed:
00:00:00.50
ora816 SamSQL :> select /*+ INDEX_ASC (T1 emp_indx_fb1) */ * from
scott.emp T1
2 where upper(job) = 'CLERK';
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ----------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800
20
7876 ADAMS
CLERK 7788
23-MAY-87 1100 20
7900 JAMES
CLERK 7698
03-DEC-81 950 30
7934 MILLER
CLERK 7782 23-JAN-82 1300 10
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1 Card=1 Bytes=87)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Byte
s=87)
2 1
INDEX (RANGE SCAN) OF 'EMP_INDX_FB1' (NON-UNIQUE) (Cost=
1 Card=1)
Without hint
ora816 SamSQL :> select *
from scott.emp T1
2 where upper(job) = 'CLERK';
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ----------- ----------
7369 SMITH
CLERK 7902
17-DEC-80 800 20
7876 ADAMS
CLERK 7788
23-MAY-87 1100 20
7900 JAMES
CLERK 7698
03-DEC-81 950 30
7934 MILLER
CLERK 7782
23-JAN-82 1300 10
Elapsed: 00:00:00.40
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0
TABLE ACCESS (FULL) OF 'EMP'
Here
you will notice that FBI is working when you use index hint , otherwise full
table scan is going on. The reason is
in my case my table is very small so optimizer is intelligent enough to do full
table scan.
Also
note on index creation statement compute statistics is important, since FBI
will not work if statistics is not gathered. If you do not want to put compute
statistics , you have to analyze table
.
Example 2:
ora816 SamSQL :> create index emp_indx_fb3 on scott.emp((((nvl(sal,0)+nvl(comm,0))+1000)*10/100)) compute statistics;
Index
created.
Elapsed:
00:00:00.70
ora816 SamSQL :> select /*+ INDEX_ASC (T1 emp_indx_fb3) */ * from
scott.emp T1
2 where (((nvl(sal,0)+nvl(comm,0))+1000)*10/100) > 400
3 /
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEPTNO
---------- ---------- ---------
---------- --------- ---------- ----------- ----------
7839 KING
PRESIDENT
17-NOV-81 5000 10
Elapsed: 00:00:00.20
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1 Card=5 Bytes=435)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=5 Byte
s=435)
2 1
INDEX (RANGE SCAN) OF 'EMP_INDX_FB3' (NON-UNIQUE) (Cost=
1 Card=5)
Example 3: Using PL/SQL functions.
Create or replace
function
Test_FBI_PLSQL (JOB in varchar2,
sal in number)
return number
DETERMINISTIC is
total_salary number ;
begin
if JOB = 'CLERK' then
total_salary :=
(sal * (10/100)) + sal;
end if;
If JOB = 'SALESMAN'
then
total_salary :=
(sal * (20/100)) + sal;
end if;
If JOB = 'MANAGER' then
total_salary :=
(sal * (50/100)) + sal;
else
total_salary :=
(sal * (30/100)) + sal;
end if;
return total_salary;
end;
/
ora816 SamSQL :> create index emp_ind_fb4 on scott.emp(TEST_FBI_PLSQL(job,sal))
compute statistics;
Index
created.
ora816 SamSQL :> select /*+ INDEX_ASC(T1 EMP_IND_FB4) */
empno,ename,job,sal,comm,test_fbi_plsql(job,sal) from scott.emp T1
where
test_fbi_plsql(job,sal) > 2000;
EMPNO ENAME
JOB SAL COMM TEST_FBI_PLSQL(JOB,SAL)
---------- ---------- ---------
---------- ---------- ------------------------
7499 ALLEN
SALESMAN 1600 300
2080
7566 JONES
MANAGER 2975 4462.5
7698 BLAKE
MANAGER 2850 4275
7782 CLARK
MANAGER 2450 3675
7788 SCOTT
ANALYST 3000 3900
7839 KING
PRESIDENT 5000 6500
7902 FORD
ANALYST 3000 3900
7 rows selected.
Elapsed: 00:00:00.61
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=1 Card=1 Bytes=20)
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Byte
s=20)
2 1 INDEX (RANGE SCAN) OF
'EMP_IND_FB4' (NON-UNIQUE) (Cost=1 Ca
rd=1)
|
Things to remember :- |
|
|
1. You must have a Enterprise Edition 2. The following parameters must set in the init parameter file QUERY_REWRITE_ENABLED=TRUE QUERY_REWRITE_INTEGRITY=TRUSTED 3. Index Statistics should be gathered/Analyzed |
|
|
|
|
Conclusion :-
Function Based Index or FBI is very imporatant for tuning point of view.
Correctly use of this may increase the application performance atleast by 30-40
%.
Reference:
Oracle Concept Manual.
Thanks to you for reading this article . Please feel free to send me your comments or
feedback at wadhwa_s@hotmail.com
Sameer wadhwa
|
Copyright 2001 – 2002 ©Sameer Wadhwa (All right reserverd) |