Oracle: Function Index

How to create an oracle Function Index
An important new feature of Oracle8i is the addition of the function-based index (hereinafter referred to as the FBI ).
With this feature, Oracle DBA can use functions or expressions in the index. These functions enable Oracle functions,
You can also use your own PL/SQL functions.

A common problem encountered by DBA during SQL statement optimization is how to optimize statements that use functions in the WHERE clause.
In the past, using a function in the WHERE clause made indexes on the table unusable, making it difficult to improve the statement performance.
Example:

The cost-based optimizer is used to create a standard B-tree index on the SURNAME column.
SQL> create index non_fbi on sale_contacts (surname );
SQL> analyze index non_fbi compute statistics;
SQL>: analyze table sale_contacts compute statistics;
SQL> SELECT count (*) FROM sale_contacts
Where upper (surname) = 'ellison ';

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 3 Card = 1 Bytes = 17)
1 0 SORT (AGGREGATE)
2 1 table access (FULL) OF 'sales _ contacts' (Cost = 3 Card = 16 Bytes = 272)
From the execution path generated by SQL * PLUS autotrace, we can see that although the index is created on the SURNAME Column Used in the WHERE clause, the full table scan is still executed. If this table is large, it will consume a lot of time.
Now we try to create an FBI index:
SQL> create index fbi on sale_contacts (UPPER (surname ));
SQL> analyze index fbi compute statistics;
SQL> analyze table sale_contacts compute statistics;
SQL> SELECT count (*) FROM sale_contacts WHERE UPPER (surname) = 'ellison ';
Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 2 Card = 1 Bytes = 17)
1 0 SORT (AGGREGATE)
2 1 INDEX (range scan) OF 'FBI '(NON-UNIQUE) (Cost = 2 Card = 381 Bytes = 6477)

From the execution plan returned by SQL * Plus, we can see that this time, Oracle does not scan the entire table, but first scans the index, because the optimizer can know that the FBI index exists.
The performance improvement caused by FBI indexes depends on the table size, the number of duplicate records in the table, and the columns used in the where clause.
It should be clear that the FBI index does not actually store the expression results in the index, but uses an "expression tree ).

The optimizer parses the expressions in SQL statements and compares them with the expressions above the FBI index. Here, SQL functions are case sensitive.
Therefore, the SQL statement must use the same case as the SQL function used when the FBI index is created. Otherwise, the FBI index cannot be used.
Therefore, you must have a good programming style during programming.
Parameters to be modified in Init. ora
The following parameters must be specified in init. ora:
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (or higher)

Authorization:
To enable a user to CREATE an fbi index, he must be granted the following permissions: create index and query rewrite, or create any index and global query rewrite.
The index user must be able to execute the function used on the FBI index. If you do not have the required permissions, the FBI index status will change to DISABLED (DBA_INDEXES ).
If the FBI index is in the DISABLED status, the DBA can handle it like this:
A: delete and recreate
B: ALTER INDEX index_name ENABLED. This Enabled can only be used for FBI indexes.
C: alter index unusable;
Note: If this index is used in a query, but the status of this FBI index is DISABLED, but the optimizer chooses to use this index, an Oracle error will be returned.
Example:
ORA error:
ERROR at line 1: ORA-30554: function-based index MYUSER. FBI is disabled.
In addition, once the status of this FBI index is Disabled, all DML operations involving index columns on this table will also fail. Unless the index status changes to UNUSABLE and SKIP_UNUSABLE_INDEXES is specified as TRUE in the initialization parameter.

Some examples:

SQL> CREATE INDEX expression_ndx
ON mytable (mycola + mycolc) * mycolb );

SQL> SELECT mycolc FROM mytable
WHERE (mycola + mycolc) * mycolb

Example of composite index:

SQL> CREATE INDEX example_ndx
ON myexample (mycola, UPPER (mycolb), mycolc );

SQL> SELECT mycolc FROM myexample
WHERE mycola = 55 and upper (mycolb) = 'Jones ';

Summary of restrictions and rules:

The following restrictions cannot be used to create an FBI index:
A) LOB Column
B) REF
C) Nested table column
D) include the above data type object

FBI indexes must comply with the following rules:

A) The cost-based optimizer must be used and the index must be analyzed after being created.
B) NULL values cannot be stored. Because no function can return NULL values under any circumstances.
C) if a user-defined PL/SQL routine becomes invalid and is used by the FBI index, the corresponding FBI index will become DISABLED.
D) The function for creating an FBI index must be deterministic. That is, the specified input always returns a definite result.
E) if the index owner does not have the permission to execute the functions used in the FBI index, the FBI index will become DISABLED.
F) You cannot use SUM or other Aggregate functions in the index creation function.
G) To re-ENABLED an index, this function must be ENABLED first.

========================================================== ========================================================== ======================
Can this statement be optimized?

Original statement:
Select B .*
From test1 a, test2 B
Where a. sflag = '-3'
And instr (',' | a. id | ',', '| B. certiid |', ')> 0;

It takes 120 seconds for the test2 table to perform a full table scan. The certiid column of test2 is actually indexed.
However, it cannot use this index or search for a table through the ROWID of the index, because there is no condition that B. certiid is equal to,
Condition instr (',' | a. id | ',' | B. certiid | ',')> 0.
Then, considering that the returned value is B. * and the INDEX only contains the CERTIID column, the INDEX FAST SCAN cannot be used.
So here we want to modify it as follows:
Select * from test2 where certiid in (
Select B. certiid
From test1 a, test2 B
Where a. sflag = '-3'
And instr (',' | a. id | ',' | B. certiid | ',')> 0 );

PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
SQL _ID gsha1gj68gacg, child number 0
-------------------------------------
Select/* + gather_plan_statistics */* from test2 where certiid in (select
B. certiid from test1 a, test2 B where a. sflag = '-3' and
Instr (',' | a. id | ',', '| B. certiid |', ')> 0)

Plan hash value: 4074250259

Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 54 | 00:00:32. 69 | 133K |
| 1 | nested loops | 1 | 1 | 54 | 00:00:32. 69 | 133K |
| 2 | VIEW | VW_NSO_1 | 1 | 1 | 54 | 00:00:32. 69 | 133K |
| 3 | hash unique | 1 | 1 | 54 | 00:00:32. 69 | 133K |
| 4 | nested loops | 1 | 1 | 54 | 00:00:28. 67 | 133K |
| 5 | table access by index rowid | test1 | 1 | 1 | 38 | 00:00:00. 01 | 49 |
| * 6 | index range scan | INDEX_SFLAG | 1 | 1 | 38 | 00:00:00. 01 | 3 |
| * 7 | index fast full scan | PK_test2 | 38 | 24575 | 54 | 00:00:25. 65 | 133K |
| 8 | table access by index rowid | test2 | 54 | 1 | 54 | 00:00:00. 01 | 167 |
| * 9 | index unique scan | PK_test2 | 54 | 1 | 54 | 00:00:00. 01 | 113 |
Bytes ----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

6-access ("A". "SFLAG" = '-3 ')
7-filter (INSTR (',' | "". "ID" | ',', '| "B ". "CERTIID" | ',')> 0)
9-access ("CERTIID" = "$ nso_col_1 ")
Here we use a subquery. The condition used in the subquery is not B. * But B. certiid. We can see that INDEX FAST FULL SCAN is used here, which is equivalent
Index when a table is scanned, the index structure is not considered. However, the 'table' of a column here is much smaller than that of all fields, so the query is faster.
The modified statement has any performance problems.
Index fast full scan | PK_test2 | 38
It runs 38 times under the nested loop mechanism. I used HASH JION to replace NESTED LOOPS, but the condition here is INSTR (',' | "". "ID" | ',', '| "B ". "CERTIID" | ',')> 0, so it is difficult to modify it.
After the statement is modified, the running time of the statement is reduced from 120 seconds to 48 seconds. Is there any optimization method?
Original execution plan:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1212030027
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU
--------------------------------------------------------------------------------
| 0 | select statement | 3943K | 1921M | 1384 K (1
| 1 | nested loops | 3943K | 1921M | 1384 K (1
| 2 | table access by index rowid | test1 | 71 | 1278 | 5 (0
| * 3 | index range scan | INDEX_SFLAG | 71 | 3 (0
| * 4 | table access full | test2 | 55470 | 26M | 19504 (1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("A". "SFLAG" = '-3 ')
4-filter (INSTR (',' | "". "ID" | ',', '| "B ". "CERTIID" | ',')> 0)

17 rows selected

Author: "selling pants to access the Internet"