Concepts and Usage of Function Based Indexes

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> (expression-list>)

expression-list> is { <column_name> | <column_expression> }

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