[IT168 Technical Document] The important new feature of Oracle8i is the index type of Function-based index (later is simply referred to as FBI). With this feature, Oracle DBA can use functions or expressions in the index. These functions can make Oracle's own functions, can also make users their own PL / SQL functions, and the like.
Oracle8i's very important new feature is an index type of Function-based Index (later simply referred to as FBI). With this feature, Oracle DBA can use functions or expressions in the index. These functions can make Oracle's own functions, can also make users their own PL / SQL functions, and the like.
A very common problem encountered in the process of SQL statement tuning is how to optimize statements that use functions in the WHERE clause. Because in the past, the use of functions in the WHERE clause will make the index created on this table not to be utilized, so that it is difficult to improve the performance of this statement.
example:
Use a cost-based optimizer, an index is a standard B tree index, and is built 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
=
In one
)
1
0
Sort (aggregate)
2
1
TABLE
ACCESS
Full
)
Of
'
Sales_Contacts
'
(COST)
=
3
Card
=
16
BYTES
=
272
)
The execution path generated from the autotrace from SQL * Plus can be seen, although we created an index on the Surname column in the WHERE clause, but still executing full table scanning. If this table is big, this time is consumed to consume a lot of time.
Now let's try to build 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
=
In one
)
1
0
Sort (aggregate)
2
1
Index
(Range Scan)
Of
'
FBI
'
(Non
-
Unique
) (COST)
=
2
Card
=
381
BYTES
=
6477
)
The execution plan returned from SQL * Plus We can see that this time, the Oracle pair is no longer scanned, but first scans the index because the optimizer can know the FBI index exist. The performance improvement that can be brought to the FBI index depends on the size of the table, the amount of recording in the table, and the columns used in the WHERE clause. One thing needs to be clear, the FBI index does not really store the result of the expression in the index, but uses an "Expression Tree).
The expression in the SQL statement is parsed by an optimizer, and the expression above the FBI index is compared. Here, the SQL function is sensitive to the case. Therefore, the function used in the SQL statement is required and the case where the SQL function to create the FBI index is consistent, otherwise this FBI index cannot be taken. Therefore, there is a good programming style when programming.
The parameters that need to be modified in the init.ra, the following parameters must be specified in Init.ra:
Query_rewrite_integrity
=
Trusted
Query_rewrite_enabled
=
True
Compatible
=
8.1
.
0.0
.
0
(
oral
Higher
Authorization:
To enable a user to create an FBI index, he must be granted: CREATE INDEX and Query Rewrite, or CREATE ANY INDEX and GLOBAL Query Rewrite these two permissions. The user of the index must be able to have the execution permission of the function used in the FBI index. If there is no corresponding permissions, then this FBI index is powered into disabled (dba_indexes).
If the FBI index status is disabled, then DBA can process:
A: Delete and rebuild
B: ALTER INDEX INDEX_NAME ENABLED. This enabled can only be used by the FBI index.
C: alter index unusable;
Note: If this index is used in a query, the status of this FBI index is disabled, but the optimizer selects this index, then a Oracle error will be returned.
example:
ORA Error:
Error At Line 1: ORA-30554: Function-based index myuser.fbi is disabled.
Moreover, once the status of this FBI index is disabled, all DML operations involving the index column will fail on this table. Unless this indexed state changes into unusable, Skip_unusable_Indexes is specified inside the initialization parameter to TRUE.
Some examples:
SQL
>
Create
Index
Expression_ndx
On
MyTable ((Mycola)
mycolc)
*
mycolb;
SQL
>
SELECT
Mycolc
From
MyTable
WHERE
(Mycola)
mycolc)
*
Mycolb
<=
256
;
Example of composite index:
SQL
>
Create
Indexexample_ndx
On
MyExample (Mycola,
Upper
(mycolb); mYcolc
SQL
>
SELECT
Mycolc
From
MyExample
WHERE
Mycola
=
55
AND
Upper
(MyColb)
=
'
Jones
'
;
Restrictions and rules summary:
For the following limitations, the FBI index cannot be created:
a) LOB column
b) Ref
c) Nested Table Columns
d) Objects containing the above data types
The FBI index must abide by the following rules:
a) The cost-based optimizer must be used, and the index must be analyzed after being created.
b) No NULL value cannot be stored. Because any function can return NULL values without any case.
c) If a user-defined PL / SQL routine is invalid, and this routine is referenced by the FBI index, then the corresponding FBI index becomes disabled.
d) Creating a FBI index function must be certain. That is, for the specified input, the determination result is always returned.
e) The owner of the index If there is no permission to execute the function used in the FBI index, then this FBI index will become disabled.
f) You cannot use SUM and other total functions inside the creation of the index.
g) Reform a disabled index into enabled, which must be first enabled.
