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

Invalid index reason

Recently, I encountered a performance problem of an Oracle SQL statement. The average running time before modifying the function was 0.3s, but after adding the new feature, the time reached 4505s. Although the amount of data in several tables is relatively large (all at the level of one million or more), the indexes are all created correctly. I don't know where it is slow. Let's start a survey.

After several times of exclusion, narrow the scope of the problem to the index, first of all, under the premise of determining that there is no problem with the index itself, consider whether the index has been used, then the new problem comes, how to know whether the specified index is enabled.

Determine whether the index is executed

1. Analytical index

That is, the index is in the monitoring state, and the index is analyzed. The ID_TT_SHOHOU_HIST_002 index is analyzed as follows

Alter index ID_TT_SHOHOU_HIST_002 monitoring usage

two。 View the information recorded in the v$object_usage view

Select * from v$object_usage

The fields are as follows:

INDEX_NAME-Index name

TABLE_NAME-Table name

MONITORING-whether it is monitored or not

USED-whether it is enabled or not

START_MONITORING-Monitoring start time

END_MONITORING-Monitoring end time

As shown in the figure above, although the index has been referenced, the speed is still very slow. Although the index is enabled, it is slowed down by some other reasons, and the investigation continues.

During the survey, I collected some reasons why the Oracle database did not use the index to share with you.

The reason for not taking the index

1. Indexes are not used when using functions on index columns

For example, TO_CHAR, TO_DATE, TO_NUMBER, TRUNC... Wait.

At this point, the solution can use a functional index, which, as the name implies, treats the fields after using the function as fields in the index as a whole.

TO_CHAR (SHOHOU_DATE, 'YYYYMMDD') in the following figure is a functional index, because the date field contains hours, minutes and seconds, which must be converted to a fixed format when comparing dates.

CREATE INDEX ID_TT_SHOHOU_HIST_003ON TT_SHOHOU_HIST (DEL_FLG,TO_CHAR (SHOHOU_DATE, 'YYYYMMDD'), SHOHOU_ID) TABLESPACE SALESPA_INDEX

two。 Implicit type conversion of indexed columns

SELECT * FROM TABLE WHERE INDEX_COLUM = 5

The INDEX_COLUM field in the above statement is of type VARCHAR2, and an implicit type conversion occurs, similar to

SELECT * FROM TABLE WHERE TO_NUMBER (INDEX_COLUM) = 5

3. Use in WHERE clause is not equal to operation

Does not mean that operations include:,! =, NOT colum > =?, NOT colum colum > 0 or colum < 0

4. Using IS NULL and IS NOT NULL

Alternative: functional indexing

Change the empty field to a non-empty c value through nvl (bforce c), and then set up the function index on the function nvl (bforce c).

Before conversion

SELECT * FROM A WHERE B = NULL

After conversion

SELECT * FROM A WHERE NVL (BMague C) = C

5. Combinatorial index

Composite index: an index consisting of multiple columns. Such as

CREATE INDEX INDEX_EMP ON EMP (COL1,COL2,COL3,...)

INDEX_EMP is the composite index and COL1 is the boot column. When querying, you can use either WHERE COL1 =? or WHERE COL1 =? AND COL2 =?, such a constraint will use the index, but WHERE COL2 =?, will not use the index, so when the constraint contains a bootstrap column, the constraint will use the combined index.

After some investigation, I used the SQL statement to retrieve the TO_CHAR (TTSH.SHOHOU_DATE, 'YYYYMMDD') formatted date on the time column, removing the minutes and seconds. After establishing the functional index, it still does not play the effect of optimization acceleration. After careful observation, it is found that after using TO_CHAR formatting time, TO_DATE is converted to time format to compare with other subquery fields. Then it quickly occurred to me that building a functional index like TO_DATE (TO_CHAR (TTSH.SHOHOU_DATE, 'YYYYMMDD'),' YYYYMMDD') resulted in a lot of improvement in running speed, from 4 seconds 5 seconds to about 0.5 seconds.

But this is only in the PL/SQL software to run SQL to improve the speed, the actual project is still running 4 minutes 5s, using statements to check the use of the index, found that there is no use of the index, but in the PL/SQL software does call the index, which is still an unsolved mystery, if there is a great god knows the reason hope to help me answer this question.

Since it cannot be called automatically, we can only force SQL to go to the specified index. The mandatory method is as follows

Add / * + INDEX (TTSH ID_TT_SHOHOU_HIST_002) * / after the SELECT statement, where TTSH is the alias of the table (when the table has an alias, the alias of the table must be added before the index)

SELECT / * + INDEX (TTSH ID_TT_SHOHOU_HIST_002) * / TO_DATE (TO_CHAR (TTSH.SHOHOU_DATE, 'YYYYMMDD'),' YYYYMMDD') AS SHOHOU_DATE FROM TT_SHOHOU_HIST TTSHWHERE.

At this point, the efficiency problem of SQL has been solved, but this is not the best solution.

First of all, there is already a functional index containing TO_CHAR (TTSH.SHOHOU_DATE, 'YYYYMMDD') in the current index, and it is difficult to create a TO_DATE (TO_CHAR (TTSH.SHOHOU_DATE,' YYYYMMDD'), 'YYYYMMDD').

Second, the method of forcing the use of an index requires specifying the index name in the SQL and changing the SQL if the index name in the database changes.

The best way is to remove the TO_DATE of the index field and uniformly use the index of TO_CHAR.

AND CAL.CALENDER = TO_DATE (TO_CHAR (TTSH.SHOHOU_DATE, 'YYYYMMDD'),' YYYYMMDD')

The above part of the statement because the CALENDER field is of DATE type, so the comparison uses TO_DATE, in fact, as long as the CALENDER into CHAR type on the line, although it seems that a lot of changes, in fact, to solve the bigger problem.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.