Three misconceptions about using an index: a function based index

function | The error of index using index three: a function based index
Using a function based index (BFI, Based function Index):

Starting with Oracle 8i, you can use a function based index to improve query performance.



Using a function based index requires several conditions:

1, the user needs to have CREATE index or create any index permission

2, users need to have query rewrite or global query Rewirte permissions

3, set system parameters Query_rewrite_enabled=true

and query_rewrite_integrity=enforced

4, set system parameters: compatible=8.1.0.0.0 or higher

5, after creating the BFI, you need to analyze the table



Take a look at the following example:

First, without establishing a function index, we see that the query does not use a single-column (dname) Index as we think:

Sql> Set Autotrace traceonly

Sql> SELECT * FROM dept where substr (dname,1,5) = ' AAA ';



No rows selected



Time used: 00:00:00.00



Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' DEPT '









Statistics

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

134 Recursive calls

0 db Block gets

Consistent gets

0 physical Reads

0 Redo Size

323 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

2 Sorts (memory)

0 Sorts (disk)

0 rows processed







The following directly establishes a function based index to see if the query can use the index we established

Sql> CREATE INDEX DEPT_ID5 on dept (substr (dname,1,5));

CREATE INDEX DEPT_ID5 on dept (substr (dname,1,5))

*

ERROR is on line 1th:

ORA-01031: Insufficient Permissions





Time used: 00:00:00.00

Sql> set Autotrace off

Sql> Col username format A10

sql> Col Privilege Format A20

Sql> select Username,privilege from User_sys_privs;



USERNAME privilege

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

DEMO Unlimited Tablespace

Public SELECT any TABLE



Time used: 00:00:00.00

Sql> Select Username, granted_role from User_role_privs;



USERNAME Granted_role

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

DEMO CONNECT

DEMO RESOURCE

Public Plustrace



Time used: 00:00:00.01



We see that although the user has the Connect and resource roles, there is still no permission to establish the function index.



We use SYSDBA identity login, to the demo user to grant create any index and global query rewrite permissions:

Sql> Conn Lunar/lunar@test1 as Sysdba

is connected.

Sql> Grant Create any index to demo;



The authorization was successful.



Time used: 00:00:00.00

Sql> Grant global query rewrite to demo;



The authorization was successful.



Time used: 00:00:00.00

Sql> Conn Demo/demo@test1

is connected.

Sql> select Username,privilege from User_sys_privs;



USERNAME privilege

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

DEMO CREATE any INDEX

DEMO GLOBAL QUERY REWRITE

DEMO Unlimited Tablespace

Public SELECT any TABLE



Time used: 00:00:00.00

Sql> Select Username, granted_role from User_role_privs;



USERNAME Granted_role

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

DEMO CONNECT

DEMO RESOURCE

Public Plustrace



Time used: 00:00:00.00



Then modify the system parameters, set Query_rewrite_enabled to True, this parameter is a dynamic parameter, set can have the effect of the vertical bar to see shadow:

Sql> conn/@test1 as Sysdba

is connected.

Sql> Show parameter query



NAME TYPE VALUE

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

query_rewrite_enabled string FALSE

Query_rewrite_integrity string Enforced

Sql> alter system set query_rewrite_enabled=true;



The system has changed.



Time used: 00:00:00.00

Sql> Show parameter query



NAME TYPE VALUE

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

query_rewrite_enabled string TRUE

Query_rewrite_integrity string Enforced



OK, then use demo user login, create function index

Sql> Conn Demo/demo@test1

is connected.

Sql> CREATE INDEX DEPT_ID5 on dept (substr (dname,1,5));



The index has been created.



Time used: 00:00:00.00

Sql> Select Index_type,index_name from user_indexes where table_name= ' DEPT ';



Index_type index_name

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

function-based NORMAL DEPT_ID5



Time used: 00:00:00.00



Visible has been created successfully.



Next, let's see if the query will use the function index we created:

Sql> Set Autotrace traceonly

Sql> SELECT * FROM dept where substr (dname,1,5) = ' AAA ';



No rows selected



Time used: 00:00:00.00



Execution Plan

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

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (full) ' DEPT '









Statistics

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

Recursive calls

0 db Block gets

Ten consistent gets

0 physical Reads

0 Redo Size

323 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed





After we analyzed the table, we saw that the query used the index as we had hoped.

sql> Analyze Table Dept COMPUTE Statistics

2 for Table

3 for all indexes

4 for all indexed columns;



The table is parsed.



Time used: 00:00:00.02

Sql> SELECT * FROM dept where substr (dname,1,5) = ' AAA ';



No rows selected



Time used: 00:00:00.02



Execution Plan

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

0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=23)

1 0 TABLE ACCESS (by INDEX ROWID) of ' DEPT ' (cost=2 card=1 byt

ES=23)



2 1 INDEX (RANGE SCAN) of ' DEPT_ID5 ' (non-unique) (Cost=1 Ca

rd=1)











Statistics

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

0 Recursive calls

0 db Block gets

1 consistent gets

0 physical Reads

0 Redo Size

323 bytes sent via sql*net to client

372 bytes received via sql*net from client

1 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

0 rows processed



Sql>



With all the statistics, we can see clearly that the proper use of the index will improve performance by several times or more.