Use the three misunderstandings of the index: the index of functions
Use a function-based index (BFI, BASED FUNCTION INDEX):
Starting with Oracle 8i, you can use a function-based index to improve query performance.
Use a function-based index that requires several conditions:
1. Users need Create INDEX or CREATE ANY INDEX permissions
2, users need Query Rewrite or Global Query RewiRTE privileges
3, set system parameters query_rewrite_enabled = TRUE
And query_rewrite_integrity = enforces
4, set system parameters: compatible =
8.1.0
.0.0 or higher
5. After the BFI is created, you need to analyze the table.
Please see the example below:
First, in the case where no function index is established, we see that the query uses a single column (DNAME) index as we think:
SQL> Set Autotrace Traceonly
SQL> Select * from Dept WHERE SUBSTR (DNAME, 1, 5) = 'AAA'
Unselected
Time: 00: 00: 00.00
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (Full) of 'DEPT'
Statistics
-------------------------------------------------- ------------
134 Recursive Calls
0 DB Block Get
20 consistent gets
0 Physical READS
0 redo size
323 BYTES SENT VIA SQL * NET to Client
372 BYtes Received Via SQL * Net from Cliant
1 SQL * NET ROUNDTRIPS TO / FROM Client
2 Sorts (Memory)
0 Sorts (Disk)
0 Rows Processed
The following directly builds 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 located on the first line:
ORA-01031: Insufficient permissions
Time: 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: 00: 00: 00.00
SQL> SELECT UserName, Granted_Role from user_role_privs; username granted_role
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Demo Connect
Demo Resource
Public Plustrace
Time: 00: 00: 00.01
We see that although the user has a CONNECT and RESOURCE role, there is still no permission to establish a function index.
We use sysdba to log in, give Demo users CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges:
SQL> Conn Lunar / Lunar @ Test1 As Sysdba
connected.
SQL> Grant Create Any Index To Demo;
Authorized success.
Time: 00: 00: 00.00
SQL> Grant Global Query Rewrite to Demo;
Authorized success.
Time: 00: 00: 00.00
SQL> Conn Demo / Demo @ Test1
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: 00: 00: 00.00
SQL> SELECT UserName, Granted_Role from User_Role_Privs;
Username granted_role
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Demo Connect
Demo Resource
Public Plustrace
Time: 00: 00: 00.00
Modify the system parameters, set the query_rewrite_enabled to true, this parameter is a dynamic parameter, and you can have the effect of the tilt of the rod after setting:
SQL> Conn / @ TEST1 AS SYSDBA
connected.
SQL> Show Parameter Query
Name Type Value
----------------------------------- --- ---------------------------
Query_rewrite_enabled String False
Query_rewrite_integrity string enforces
SQL> ALTER System Set Query_rewrite_enabled = true;
The system has changed.
Time: 00: 00: 00.00
SQL> Show Parameter Query
Name Type Value
----------------------------------- --- ---------------------------
Query_rewrite_enabled string TrueQuery_rewrite_integrity string enforces
Ok, use the DEMO user to log in, create a function index.
SQL> Conn Demo / Demo @ Test1
connected.
SQL> CREATE INDEX DEPT_ID5 on DEPT (SUBSTR (DNAME, 1, 5));
Index has been created.
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: 00: 00: 00.00
It can be seen that has been created.
Below, we look at whether the query will use the function index we created:
SQL> Set Autotrace Traceonly
SQL> Select * from Dept WHERE SUBSTR (DNAME, 1, 5) = 'AAA'
Unselected
Time: 00: 00: 00.00
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (Full) of 'DEPT'
Statistics
-------------------------------------------------- ------------
29 Recursive Calls
0 DB Block Get
10 consistent gets
0 Physical READS
0 redo size
323 BYTES SENT VIA SQL * NET to Client
372 BYtes Received Via SQL * Net from Cliant
1 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
0 Rows Processed
After we analyze the table, we see that the query uses an index as we hope.
SQL> Analyze Table Dept Compute Statistics
2 for Table
3 for all indexes
4 for all indexed columns;
The table has been analyzed.
Time: 00: 00: 00.02
SQL> Select * from Dept WHERE SUBSTR (DNAME, 1, 5) = 'AAA'
Unselected
Time: 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 bytes = 23)
2 1 Index (Range Scan) of 'DEPT_ID5' (Non-Unique) (COST = 1 CA
Rd = 1)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
1 Consistent Gets
0 Physical READS
0 redo size
323 BYTES SENT VIA SQL * NET to Client
372 BYtes Received Via SQL * Net from Cliant
1 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
0 Rows Processed
SQL>
Through all STATISTICs, we can clearly see that the appropriate use index will be a performance improvement of several times or more.
