Concepts and Usage of Function Based Indexes [ID 66277.1]

 

Concepts and Usage of Function Based Indexes [ID 66277.1]
	修改时间 21-OCT-2010     类型 BULLETIN     状态 PUBLISHED

 

Checked for relevance on 21-OCT-2009

 

PURPOSE

  This documentwill familiarize the reader with the concepts of the function

  based index,its usage and syntax.

 

SCOPE & APPLICATION

  For usersneeding further information about function based indexes.

 

 

 

Concepts and Usage of Function Based Indexes:

=============================================

 

I. INTRODUCTION:

 

In a typical example where employee names are storedin a table and the

statement similar to "name='ALAN SMITH'" isin the where clause, for the optimizer to

choose an index, an index needs to be created on thecolumn 'name'.

 

Using the operation on the index column, e.g."UPPER(name) = 'ALAN SMITH'", the

optimizer would NOT choose the above index created onname column.

 

Function-based indexes introduced in Oracle release8.1 provides an efficient

mechanism to evaluate predicates involving functionsas above. Now the

optimizer can use the index range scan for querieswith expressions in where

clause.

 

The main features of Function-based Indexes are:

 

   1. Used bycost-based optimizer.

 

   2. Can becreated either as B*Tree or bitmap index.

 

   3. Index canbe build on an arithmetic expression or expression containing

      PL/SQL,package functions, C callout or SQL built-in functions.

 

   4. Optimizercan estimate selectivity more accurately if the expressions

      arematerialized in a function-based index. Range scan can be used for

      querieswith expression in where clause and has index build on the

      expressionused.

 

   5. Providesefficient linguistic collation to use NLS sort index.

 

   6. Tomaterialize computational intensive expressions so that the value

      of theexpression is not computed when processing SELECT/DELETE but

      still hasto do for INSERT/UPDATE.

 

   7. Improvesquery performance.

 

   8. Indexescan be created on object columns and REF columns by using

      methodsdefined for the object.

 

   9. Functionbased indexing is only available in the Enterprise Edition and

      is notavailable 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 wasattempted in the Standard Edition. Also, note that bitmap indexes

      are alsonot available in the Standard Edition, but only in the

      EnterpriseEdition.

 

 

II. CREATE A FUNCTION-BASED INDEX:

 

The function-based index can be used to build an indexon a function of a

column in the table.

 

    CREATE[UNIQUE | BITMAP] INDEX

        ON  ()

 

     is {  |  }

 

    BITMAPkeyword specifies that the index is bitmap index.

 

Here the syntax focus is primarily on thefunction-based index options.

For the complete syntax of create index, refer to theSQL Reference Manual.

 

NOTE: Users need to have QUERY REWRITE systemprivilege to create

      afunction-based index in a users schema, GLOBAL QUERY REWRITE

      privilegeto create an index on a table in another users' schema.

        

      GRANTQUERY REWRITE TO SCOTT; 

 

      Example:

         CREATEINDEX emp_name_index ON emp ( UPPER(ename)) ;

         CREATEINDEX emp_sal_index ON emp( SAL + COMM , empno) ;

         CREATEINDEX emp_func_index ON emp( ( getlastname(ename) , empno) ;

 

 

III. OPTIMIZER:

 

Function based indexes are used only by the cost basedoptimizer. So, the

tables need to be analyzed using estimate or computestatistics. 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, thefollowing must be set.

 

QUERY_REWRITE_ENABLED = TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

COMPATIBLE must be 8.1.0.0.0 or greater (recommendedto be set to the current

version of the database).  The user creating the index must be grantedCREATE

INDEX and QUERY REWRITE, or CREATE ANY INDEX andGLOBAL QUERY REWRITE.

 

Note: From 9.2.0.4 onwards enhancement request through Bug 2799028 removes

the requirement of QUERY_REWRITE privilege whencreating functional indexes,

and removes the requirement that theQUERY_REWRITE_ENABLED and

QUERY_REWRITE_INTEGRITY parameters be set whenquerying the index.

 

 

In order to use the function based index, the indexmust be analyzed after

it is created and the function must not return anyNULL values as NULLs

are not stored in any index.

 

NOTE: This manual is located both on the GenericDocumentation 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 isusing the index range

scan for the predicate where UPPER(emp_name) ='ALANSMITH'

 

Example:

 

NOTE:Run $ORACLE_HOME/rdbms/admin/utlxplan.sql tocreate plan table

 

     CREATETABLE emp1(emp_name VARCHAR2(20),

                      emp_no NUMBER(10),dname VARCHAR2(20));

 

     CREATEINDEX i3 ON emp1(UPPER(emp_name));

 

     ANALYZETABLE emp1 COMPUTE STATISTICS

     /

     DELETEplan_table

     /

     EXPLAINPLAN SET statement_id='Test1' FOR

     SELECTemp_name FROM emp1 WHERE UPPER(emp_name) = 'ALAN SMITH'

     /

     SELECTLPAD(' ',2*level-2)||operation||' '||options||' '||object_name

     query_plan

     FROMplan_table

     WHEREstatement_id='Test1'

     CONNECT BYprior id = parent_id

     START WITHid = 0 order by id

     /

 

     QUERY_PLAN

    ---------------------------------------

     SELECTSTATEMENT

       TABLEACCESS BY INDEX ROWID EMP1

         INDEXRANGE SCAN I3

 

 

IV. LINGUISTIC INDEXES:

 

When using function-based indexes, the NLS sort ordercan be used. For example,

using NLSSORT function, we can order the details usingcollating sequence

of GERMAN, JAPANESE etc.

 

An index can be created with collating sequence GERMANas follows:

 

  CREATE INDEXemp4_i1 ON emp4(NLSSORT(ename, 'NLS_SORT=German'));

 

If the session NLSSORT is German, and a query on thistable using emp4_i1

would return the data order by ename and in sort orderGerman collating

sequence would be used.

 

For the linguistic search to be used instead of Binarysearch, 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 ACCESSBY INDEX ROWID EMP4

    INDEX RANGESCAN EMP4_I1

 

 

Multiple Linguistic indexes:

 

If you store character data of multiple languages intoone database, you may

want to create multiple linguistic indexes for onecolumn. This will improve

the performance of the linguistic sort for a specificcolumn for multiple

languages and is a powerful feature for multilingualdatabases. Multilingual

support is best served by a universal character setsuch as Unicode.

 

 

V. USING SQL, PLSQL FUNCTIONS IN FUNCTION BASED INDEX:

 

PL/SQL functions, and package functions can be used increating function based

index.

 

The PL/SQL functions used in defining function-basedindex 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/SQLfunction. The index can be

invalidated or made unusable by changes to thefunction. The index is marked

DISABLED, if there are changes to the function or thefunction is recreated.

The time-stamp of the function is used to validate theindex.

 

To enable the index after the function is created, ifthe signature of the

function is same as before:

 

   ALTERINDEX  ENABLE;

 

If the signature of the functions is changed, to makethe changes effective

in the index, the index need to be revalidated to makeit valid and enable.

 

   ALTERINDEX  REBUILD;

 

 

NOTE:

=====

 

    For PL/SQLfunctions returning VARCHAR2 or RAW datatypes the output

    size shouldbe limited using SUBSTR for creating the index and referencing

    the functionin sub-queries;

 

 

Example:

 

  a) TheOptimizer Goal need to be COST based.

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

         ALTERSESSION SET OPTIMIZER_GOAL=FIRST_ROWS

 

  b) CreatePL/SQL function and declare it as deterministic

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        CREATEOR REPLACE FUNCTION myUpper(var in VARCHAR2)

                                   RETURNVARCHAR2 DETERMINISTIC AS

        BEGIN

           RETURN UPPER(var);

        END;

        /

 

  c) Sessionvariables

  ~~~~~~~~~~~~~~~~~~~~

       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 incase of materialized views.

             

        Setsession variable for cost based optimizer to choose the

       function-based index.

 

        ALTERSESSION SET QUERY_REWRITE_ENABLED = TRUE;

        ALTERSESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

 

 

  d) Createfunction-based index and analyze the table

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        CREATEINDEX emp1_i1 ON emp1(SUBSTR(myupper(emp_name),1,20)) ;

 

        ANALYZETABLE emp1 COMPUTE STATISTICS;

 

  NOTE: InOracle 8.1,the statistics can be computed at the time of creating

        index.

 

        Forexample:

        CREATEINDEX emp1_i1 ON emp1(SUBSTR(myupper(emp_name),1,20))

        COMPUTESTATISTICS;

 

  e) Verify theexplain plan for the statement

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        EXPLAINPLAN SET statement_id='Test1' FOR

        SELECTemp_name FROM emp1 WHERE SUBSTR(myupper(emp_name),1,20) = 'ALAN SMITH';

        SELECTLPAD(' ',2*LEVEL-2)||operation||' '||options||' '||object_name

       query_plan

        FROMplan_table

        WHEREstatement_id='Test1'

        CONNECTBY PRIOR id = parent_id

        STARTWITH id = 0 ORDER BY id

        /

 

        Theoutput of the explain plan

 

       QUERY_PLAN

       ------------------------------------------------------------------

        SELECTSTATEMENT

          TABLEACCESS BY INDEX ROWID EMP1

           INDEX RANGE SCAN EMP1_I1

 

 

VI. FUNCTION BASED INDEX ON OBJECT TYPE COLUMNS USINGMETHODS:

 

Consider the following example to discuss thefunction-based index on TYPE

objects:

 

      a) CreateADT objects:

     ~~~~~~~~~~~~~~~~~~~~~~

      drop tableprojects ;

      CREATE orREPLACE TYPE proj_t AS OBJECT

      (projid  NUMBER ,

        projnameVARCHAR2(20),

        projcostNUMBER(12,2) ,

        MEMBERFUNCTION actualcost(p_projid IN NUMBER, projcost IN NUMBER)

           RETURN NUMBER DETERMINISTIC,

        MEMBERFUNCTION effectivecost(p_projid IN NUMBER , projcost IN NUMBER)

           RETURN NUMBER DETERMINISTIC,

        MAPMEMBER FUNCTION proj_map RETURN NUMBER DETERMINISTIC

      )

      /

      showerrors

      CREATE ORREPLACE TYPE BODY proj_t

      AS

       MEMBERFUNCTION actualcost(p_projid IN NUMBER, projcost IN NUMBER)

         RETURNNUMBER IS

         BEGINRETURN projcost *2 ; END;

 

       MEMBERFUNCTION effectivecost ( p_projid IN NUMBER, projcost IN NUMBER)

         RETURNNUMBER IS

         BEGINRETURN projcost*3 ; END;

 

 

 

       MAPMEMBER FUNCTION  proj_map RETURN NUMBERIS

        BEGINRETURN projid ; END;

       END;

      /

      CREATETABLE projects(ename VARCHAR2(20), project proj_t);

 

      INSERTINTO  projects VALUES('ALAN QUEST',

                           proj_t(101,'Advertisement', 123)) ;

      INSERTINTO  projects VALUES('Micheal Turner',

                           proj_t(99,'Creation', 53)) ;

      INSERTINTO  projects VALUES('Chris Joyner',

                           proj_t(123,'Distribution', 68)) ;

 

     b) CreateFunction Based Index:

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

      CREATEINDEX p1 ON projects(project.actualcost(project.projid,

     project.projcost));

 

      Index onthe MAP method can be created as follows:

 

      CREATEINDEX p4 ON projects(project.proj_map()) ;

 

     c) VerifyThe Optimizer Plan:

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     ANALYZETABLE projects COMPUTE STATISTICS;

     EXPLAINPLAN SET statement_id = 'i1' FOR

     SELECTb.ename, b.project.projname FROM projects b WHERE

     b.project.actualcost(b.project.projid, b.project.projcost) > 20

     /

     SELECTLPAD(' ',2*level-2)||operation||' '||options||' '||object_name

     query_plan

     FROMplan_table

     WHEREstatement_id='i1'

     CONNECT BYPRIOR id = parent_id

     START WITHid = 0 ORDER BY id

     /

 

     QUERY_PLAN

    ------------------------

     SELECTSTATEMENT

       TABLEACCESS BY INDEX ROWID PROJECTS

         INDEXRANGE SCAN P1

 

 

VII. DATA DICTIONARY:

 

1. For function-based indexes the index type isFUNCTION-BASED NORMAL.

 

2. To identify the function based indexes:

 

     SELECTindex_name, table_name, funcidx_status FROM user_indexes ;

 

     INDEX_NAMETABLE_NAME                     FUNCIDX_

     ---------- --------------------------------------

     I1         EMP                            ENABLED

     SALI1      EMP

 

    where I1 isfunction based index and SALI1 is regular index.

 

    Thefollowing query will select all the function based indexes.

   

    SELECTindex_name, table_name, status FROM user_indexes

     WHEREFUNCIDX_STATUS = 'ENABLED' ;

    

   

3. The column_name in user_ind_columns for thefunction based index is like

   'SYS_NCxxxx'.

 

    SELECTindex_name, table_name , column_name FROM user_ind_columns ;

 

    INDEX_NAMETABLE_NAME                    COLUMN_NAME

    ---------------------------------------- -----------------

    I1         EMP                            SYS_NC00009$

 

4. Function-based indexes can be of status VALID orDISABLED.

 

 

VIII. RESTRICTIONS:

 

Function-based indexes should reference only columnsin a row of a table.  You

therefore cannot index:

a) LOB columns,

b) Nested table column

 

Further,

c) Aggregate functions are not allowed in theexpressions of the index.

   Example: SUM,AVG, etc.

d) As you have to generate statistics after creatingfunction based indexes,

   it can onlybe used with Cost Based optimizer. The rule based optimizer

   will neveruse function-based indexes.

e) Since function cannot return NULL you cannot storenull values

f) The function should return the same value for aninput. In other words it

   should bedeterministic

g) The index can only be enabled if the signature ofthe function is same as

   before (i.ewhen it was created). If the signature of the functions changes

   then theindex needs to be revalidated by using the rebuild option:

   ALTERINDEX  REBUILD;

 

 

Search Words:

=============

 

ORA-439