Oracle index Suppression

Oracle index Suppression

Preface: in many cases of Oracle Database optimization, you can increase the database performance by adding indexes. However, in some cases, DBA cannot do anything about it, because all the indexes have been created, but the index cannot be taken due to the SQL statement written by the developer, You need to modify the statement in this case;

Test Environment Description:

Understanding ArcSDE indexes by Oracle Indexes

How to create an optimal index using Oracle Indexing Technology

Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Oracle index primary key affects query speed

Oracle index Scanning

Case 1: Use of TO_CHAR and TO_DATE

The following two statements implement the same functions, but the two execution plans are different. One of them will go through the index, and the other will not go through the index:

Select count (*) From john where lastruntime> TO_DATE ('2017-03-05 11:00:00 ', 'yyyy-MM-dd HH24: mi: ss ');

Select count (*) From john where TO_CHAR (LASTRUNTIME, 'yyyy/MM/dd HH24: mi: ss')> '2017-03-05 11:00:00 ';

 

Cause: the JOHN_TIME index records the value of DATA instead of the value after to_char. Therefore, the statement will be indexed later, and Statement 2 will be scanned throughout the table;

 

Case 2: use expressions in conditions

Select count (*) From john where lastruntime> SYSDATE-100;

Select count (*) From john where LASTRUNTIME-100> SYSDATE;

 

Cause: the JOHN_TIME index records the value of DATA instead of the value after DATA-100;

Conclusion: 1. Do not add correspondence before fields easily;

2. Try not to embed fields into expressions;

All in all: the column with an index cannot perform any related operations, but it is placed separately on the side of the equation;