Three misconceptions about using an index: a function-based index--oracle__c language

The myth of using an index: index <?xml:namespace prefix = o ns = "Urn:schemas-microsoft-com:office:office"/>

Http://blog.csdn.net/lunar2000/archive/2004/11/24/193479.aspx

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=<?xml:namespace prefix = st1 ns = "Urn:schemas-microsoft-com:office:smarttags"/>8.1.0.0.0 or More high

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.

 

used Time : 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