Subject: Concepts and Usage of Function Based Indexes
Doc ID: Note:66277.1 Type: BULLETIN
Last Revision Date: 30-MAY-2006 Status: PUBLISHED
PURPOSE
This document will familarize the reader with the concepts of the function
based index, its usage and syntax.
SCOPE & APPLICATION
For users needing further information about function based indexes.
Concepts and Usage of Function Based Indexes:
=============================================
I. INTRODUCTION:
In a typical example where employee names are stored in a table and the
statement "like name='ALAN SMITH'" is in the where clause, for the optimizer to
choose an index, an index needs to be created on the column 'name'.
Using the operation on the index column "like UPPER(name) = 'ALAN SMITH'", the
optimizer would NOT choose the above index created on name column.
Function-based indexes introduced in Oracle release 8.1 provides an efficient
mechanism to evaluate predicates involving functions as above. Now the
optimizer can use the index range scan for queries with expressions in where
clause.
The main features of Function-based Indexes are:
1. Used by cost-based optimizer.
2. Can be created either as B*Tree or bitmap index.
3. Index can be build on an arithmetic expression or expression containing
PL/SQL, package functions, C callout or SQL built-in functions.
4. Optimizer can estimate selectivity more accurately if the expressions
are materialized in a function-based index. Range scan can be used for
queries with expression in where clause and has index build on the
expression used.
5. Provides efficient linguistic collation to use NLS sort index.
6. To materialize computational intensive expressions so that the value
of the expression is not computed when processing SELECT/DELETE but
still has to do for INSERT/UPDATE.
7. Improves query performance.
8. Indexes can be created on object columns and REF columns by using
methods defined for the object.
9. Function based indexing is only available in the Enterprise Edition and
is not available in the Standard Edition until 9.2.x and higher. Prior
to 9.2.x, an ORA-00439 error "function not enabled" would result if its
use was attempted in the Standard Edition. Also, note that bitmap indexes
are also not available in the Standard Edition, but only in the
Enterprise Edition.
II. CREATE A FUNCTION-BASED INDEX:
The function-based index can be used to build an index on a function of a
column in the table.
CREATE [UNIQUE | BITMAP] INDEX <index_name>
ON <tablename> (
BITMAP keyword specifies that the index is bitmap index.
Here the syntax focus is primarily on the function-based index options.
For the complete syntax of create index, refer to the SQL Reference Manual.
NOTE: Users need to have QUERY REWRITE system privilege to create
a function-based index in a users schema, GLOBAL QUERY REWRITE
privilege to create an index on a table in another users' schema.
GRANT QUERY REWRITE TO SCOTT;
Example:
CREATE INDEX emp_name_index ON emp ( UPPER(ename)) ;
CREATE INDEX emp_sal_index ON emp( SAL + COMM , empno) ;
CREATE INDEX emp_func_index ON emp( ( getlastname(ename) , empno) ;
III. OPTIMIZER:
Function based indexes are used only by the cost based optimizer. So, the
tables need to be analyzed using estimate or compute statistics. The rule
based optimizer will never use function-based indexes.
As documented in the Oracle9i Database Concepts (9.2.0) manual, page 15-8:
For a function based index to be created, the following must be set.
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
COMPATIBLE must be 8.1.0.0.0 or greater (recommended to be set to the current
version of the database). The user creating the index must be granted CREATE
INDEX and QUERY REWRITE, or CREATE ANY INDEX and GLOBAL QUERY REWRITE.
In order to use the function based index, the index must be analyzed after
it is created and the function must not return any NULL values as NULLs
are not stored in any index.
NOTE: This manual is located both on the Generic Documentation CD that was
shipped with the server software, and can be found at
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524.pdf.
In the following example, notice that the optimizer is using the index range
scan for the predicate where UPPER(emp_name) ='ALAN SMITH'
Example:
NOTE:Run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create plan table
CREATE TABLE emp1(emp_name VARCHAR2(20),
emp_no NUMBER(10),dname VARCHAR2(20));
CREATE INDEX i3 ON emp1(UPPER(emp_name));
ANALYZE TABLE emp1 COMPUTE STATISTICS
/
DELETE plan_table
/
EXPLAIN PLAN SET statement_id='Test1' FOR
SELECT emp_name FROM emp1 WHERE UPPER(emp_name) = 'ALAN SMITH'
/
SELECT LPAD(' ',2*level-2)||operation||' '||options||' '||object_name
query_plan
FROM plan_table
WHERE statement_id='Test1'
CONNECT BY prior id = parent_id
START WITH id = 0 order by id
/
QUERY_PLAN
---------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP1
INDEX RANGE SCAN I3
IV. LINGUISTIC INDEXES:
When using function-based indexes, the NLS sort order can be used. For example,
using NLSSORT function, we can order the details using collating sequence
of GERMAN, JAPANESE etc.
An index can be created with collating sequence GERMAN as follows:
CREATE INDEX emp4_i1 ON emp4(NLSSORT(ename, 'NLS_SORT=German'));
If the session NLSSORT is German, and a query on this table using emp4_i1
would return the data order by ename and in sort order German collating
sequence would be used.
For the linguistic search to be used instead of Binary search, set the session
parameter NLS_COMP=ANSI.
ALTER SESSION SET NLS_COMP=ANSI;
Change the language to GERMAN.
ALTER SESSION SET NLS_LANGUAGE=GERMAN;
For a query like:
SELECT ename FROM emp4 WHERE ename = 'MIKE'
ORDER BY ename;
the optimizer plan is as shown below:
QUERY_PLAN
--------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP4
INDEX RANGE SCAN EMP4_I1
Multiple Linguistic indexes:
If you store character data of multiple languages into one database, you may
want to create multiple linguistic indexes for one column. This will improve
the performance of the linguistic sort for a specific column for multiple
languages and is a powerful feature for multilingual databases. Multilingual
support is best served by a universal character set such as Unicode.
V. USING SQL, PLSQL FUNCTIONS IN FUNCTION BASED INDEX:
PL/SQL functions, and package functions can be used in creating function based
index.
The PL/SQL functions used in defining function-based index must be declared as
DETERMINISTIC. The owner of the index should have EXECUTE privilege on the
function being used to create the index.
The index is dependent on the state of PL/SQL function. The index can be
invalidated or made unusable by changes to the function. The index is marked
DISABLED, if there are changes to the function or the function is recreated.
The time-stamp of the function is used to validate the index.
To enable the index after the function is created, if the signature of the
function is same as before:
ALTER INDEX <indexname> ENABLE;
If the signature of the functions is changed, to make the changes effective
in the index, the index need to be revalidated to make it valid and enable.
ALTER INDEX <indexname> REBUILD;
NOTE:
=====
For PL/SQL functions returning VARCHAR2 or RAW datatypes the output
size should be limited using SUBSTR for creating the index and referencing
the function in sub-queries;
Example:
a) The Optimizer Goal need to be COST based.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROWS
b) Create PL/SQL function and declare it as deterministic
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE FUNCTION myUpper(var in VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC AS
BEGIN
RETURN UPPER(var);
END;
/
c) Session variables
~~~~~~~~~~~~~~~~~~~~
QUERY_REWRITE_ENABLED(true,false),
QUERY_REWRITE_INTEGRITY (trusted, enforced, stale_tolerated)
determines the optimizer to use the function-based index with
expressions using SQL functions, user defined functions.
TRUSTED: Oracle allows rewrites using relationships that have
been declared.
ENFORCED: Oracle enforces and gaurantees consistency and integrity.
STALE_TOLERATED: Oracle allows rewrites using unenforced relation ships.
Used in case of materialized views.
Set session variable for cost based optimizer to choose the
function-based index.
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
d) Create function-based index and analyze the table
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE INDEX emp1_i1 ON emp1(SUBSTR(myupper(emp_name),1,20)) ;
ANALYZE TABLE emp1 COMPUTE STATISTICS;
NOTE: In Oracle 8.1,the statistics can be computed at the time of creating
index.
For example:
CREATE INDEX emp1_i1 ON emp1(SUBSTR(myupper(emp_name),1,20))
COMPUTE STATISTICS;
e) Verify the explain plan for the statement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
EXPLAIN PLAN SET statement_id='Test1' FOR
SELECT emp_name FROM emp1 WHERE SUBSTR(myupper(emp_name),1,20) = 'ALAN SMITH';
SELECT LPAD(' ',2*LEVEL-2)||operation||' '||options||' '||object_name
query_plan
FROM plan_table
WHERE statement_id='Test1'
CONNECT BY PRIOR id = parent_id
START WITH id = 0 ORDER BY id
/
The output of the explain plan
QUERY_PLAN
------------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP1
INDEX RANGE SCAN EMP1_I1
VI. FUNCTION BASED INDEX ON OBJECT TYPE COLUMNS USING METHODS:
Consider the following example to discuss the function-based index on TYPE
objects:
a) Create ADT objects:
~~~~~~~~~~~~~~~~~~~~~~
drop table projects ;
CREATE or REPLACE TYPE proj_t AS OBJECT
( projid NUMBER ,
projname VARCHAR2(20),
projcost NUMBER(12,2) ,
MEMBER FUNCTION actualcost(p_projid IN NUMBER, projcost IN NUMBER)
RETURN NUMBER DETERMINISTIC,
MEMBER FUNCTION effectivecost(p_projid IN NUMBER , projcost IN NUMBER)
RETURN NUMBER DETERMINISTIC,
MAP MEMBER FUNCTION proj_map RETURN NUMBER DETERMINISTIC
)
/
show errors
CREATE OR REPLACE TYPE BODY proj_t
AS
MEMBER FUNCTION actualcost(p_projid IN NUMBER, projcost IN NUMBER)
RETURN NUMBER IS
BEGIN RETURN projcost *2 ; END;
MEMBER FUNCTION effectivecost ( p_projid IN NUMBER, projcost IN NUMBER)
RETURN NUMBER IS
BEGIN RETURN projcost*3 ; END;
MAP MEMBER FUNCTION proj_map RETURN NUMBER IS
BEGIN RETURN projid ; END;
END;
/
CREATE TABLE projects(ename VARCHAR2(20), project proj_t);
INSERT INTO projects VALUES('ALAN QUEST',
proj_t(101, 'Advertisement', 123)) ;
INSERT INTO projects VALUES('Micheal Turner',
proj_t(99, 'Creation', 53)) ;
INSERT INTO projects VALUES('Chris Joyner',
proj_t(123, 'Distribution', 68)) ;
b) Create Function Based Index:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE INDEX p1 ON projects(project.actualcost(project.projid,
project.projcost));
Index on the MAP method can be created as follows:
CREATE INDEX p4 ON projects(project.proj_map()) ;
c) Verify The Optimizer Plan:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ANALYZE TABLE projects COMPUTE STATISTICS;
EXPLAIN PLAN SET statement_id = 'i1' FOR
SELECT b.ename, b.project.projname FROM projects b WHERE
b.project.actualcost(b.project.projid, b.project.projcost) > 20
/
SELECT LPAD(' ',2*level-2)||operation||' '||options||' '||object_name
query_plan
FROM plan_table
WHERE statement_id='i1'
CONNECT BY PRIOR id = parent_id
START WITH id = 0 ORDER BY id
/
QUERY_PLAN
------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID PROJECTS
INDEX RANGE SCAN P1
VII. DATA DICTIONARY:
1. For function-based indexes the index type is FUNCTION-BASED NORMAL.
2. To identify the function based indexes:
SELECT index_name, table_name, funcidx_status FROM user_indexes ;
INDEX_NAME TABLE_NAME FUNCIDX_
---------- ------------------------------ --------
I1 EMP ENABLED
SALI1 EMP
where I1 is function based index and SALI1 is regular index.
The following query will select all the function based indexes.
SELECT index_name, table_name, status FROM user_indexes
WHERE FUNCIDX_STATUS = 'ENABLED' ;
3. The column_name in user_ind_columns for the function based index is like
'SYS_NCxxxx'.
SELECT index_name, table_name , column_name FROM user_ind_columns ;
INDEX_NAME TABLE_NAME COLUMN_NAME
---------- ------------------------------ -----------------
I1 EMP SYS_NC00009$
4. Function-based indexes can be of status VALID or DISABLED.
VIII. RESTRICTIONS:
Function-based indexes should reference only columns in a row of a table. You
therefore cannot index:
a) LOB columns,
b) Nested table column
Further,
c) Aggregate functions are not allowed in the expressions of the index.
Example: SUM, AVG, etc.
d) As you have to generate statistics after creating function based indexes,
it can only be used with Cost Based optimizer. The rule based optimizer
will never use function-based indexes.
e) Since function cannot return NULL you cannot store null values
f) The function should return the same value for an input. In other words it
should be deterministic
g) The index can only be enabled if the signature of the function is same as
before (i.e when it was created). If the signature of the functions changes
then the index needs to be revalidated by using the rebuild option:
ALTER INDEX <indexname> REBUILD;
Search Words:
=============
ORA-439
No comments:
Post a Comment