[Reprint] FBI index
http://www.look4bug.com/Items/xtzq/...00-299/286.htmlfbi Index ---> [Author: net] very important new feature is the addition of Oracle8i function-based index that The type of index (referred to as FBI later). 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 as 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 = 17) 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 used in the WHERE clause, but still executing a full table scan. If this table is big, this time is consumed to consume a lot of time. Now we try to establish a 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 '(cost = 2 card = 381 bytes = 6477) Return from SQL * Plus We can see that this time, the Oracle pair is no longer a full table scan, but first scans the index, because The optimizer can know that the FBI index exists. 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 need to be modified inside in init.ra must be specified inside in Init.ra: query_rewrite_integrity = trusted query_rewrite_enabled = true compatible = 8.1.0.0.0 (or higher) Authorization: To make a user can create a FBI index, he The following permissions 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, the 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 an 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, then all DML operations involving the index column will fail. . 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; an example of a composite index: SQL> CREATE INDEX example_ndx ON myexample (Mycola, Upper (MyColb), MyColc); SQL> Select mycolc from myexample where mycolc from myexample where mycola = 55 and upper (mycolb) = 'Jones'; restrictions and rules summary: For the following limitations, FBI index cannot be created: a) LOB column b) REF C) NESTED TABLE L. The object FBI index containing the above data type must comply with the following rules: a) The cost-based optimizer must be used, and the index must be analyzed after the index is not possible. Because any function can return NULL values without any case.
c) If a user-defined PL / SQL routine is invalid, and this routine is incorporated by the FBI index, the corresponding FBI index becomes DISABLED D) Creating the FBI index function must be certain. That is, for the specified input, the determination result is always returned. e) The owner of the index does not have the execution permission of the function used in the FBI index, then this FBI index will become a disabled. f) Inside the created cable, you cannot use the SUM and other total functions. g) Reform a disabled index into enabled, which must be first enabled. __________________oracle Document: http // Tahiti.Oracle.com Message Board: www.bluemask.net/services/gb/index1.asp? BID = 484mail: jlandzpa @ itpub.netzpaandjl @ Hotmail.com
