Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is a functional index". Interested friends may wish to take a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is a functional index"!

Functional index

In Oracle, there is a special type of index called functional index (Function-Based Indexes,FBI), which creates an index based on the results of calculating the columns in the table. Functional indexes improve query performance without modifying the logic of the application. Without a functional index, any query that performs a function on a column cannot use the index of that column. The database uses the function index only when the function is included in the query. A functional index can be an B-Tree index or a bitmap index.

Trample on the background

1. The data volume of a transaction table in the production environment is about 20w. When querying according to specific conditions, it is found that the query is particularly slow.

(test environment data)

two。 Check the table structure and find that the date field is not indexed

3. I thought it would be OK to index the date directly.

Create index IX_MESSAGE_MA_CREAT_TIME on MESSAGE (MA_CREAT_TIME) tablespace UTMSINDEX pctfree 10 initrans 2 maxtrans 255storage (initial 9m next 1m minextents 1 maxextents unlimited)

4. But when querying again, we found that the query was still very slow. By looking at the Oracle explanation plan, we found that the date index was not used.

5. Create a functional index

Create index IX_MESSAGE_MA_CREAT_TIME on MESSAGE (to_char (MA_CREAT_TIME, 'yyyy-mm-dd')) tablespace UTMSINDEX pctfree 10 initrans 2 maxtrans 255storage (initial 9m next 1m minextents 1 maxextents unlimited)

6. Using Oracle to explain the plan to query again, the date field normally uses the functional index, the query efficiency is greatly improved.

Matters needing attention

1. Functional indexes should be created in accordance with those in use.

Created as: to_char (MA_CREAT_TIME, 'yyyy-mm-dd')

You also need to use to_char (MA_CREAT_TIME, 'yyyy-mm-dd') when querying

To_char (MA_CREAT_TIME, 'YYYY-MM-DD') will fail in this way

Any inconsistency will cause the index to fail, as do other functions.

two。 Custom functions are not recommended

If the custom function used by the function index fails or the owner of the function index does not have the execution permission of the function used in the function index, it will cause an ORA-06575 error

Modify the custom function again and pass the compilation without error before it can be used normally.

3. The function that creates the function index must be deterministic. That is, there is always a definite result for the specified input.

4. Functions that create indexes cannot use aggregate functions such as SUM, COUNT, and so on.

5. You cannot create a functional index on a column of type LOB, a NESTED TABLE column.

6. You cannot use nondeterministic functions such as SYSDATE, USER, and so on.

7. The DETERMINISTIC keyword must be explicitly declared for any user-defined function, or it will result in an ORA-30553:the funciton is not deterministic error.

At this point, I believe you have a deeper understanding of "what is a functional index". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.