What are the scenarios of index invalidation? When will the index expire?


Although you have built an index on this column and the query criteria are also index columns, the final execution plan does not take its index. Here are some key points causing this problem.

Column to column comparison

In a table, two columns (ID and c_id) have separate indexes. The following query criteria will not go through the index

select * from test where id=c_id;

In this case, it will be considered that it is better to take a full table scan.

Null value condition exists

When designing the database table, we should try our best to avoid the occurrence of null value. If we have to avoid the occurrence of null value, we should also give a default value. The numeric type can be given 0, – 1 and so on. Sometimes there is a problem with empty string, we should give a space or other.

If the index column is nullable, it is likely that it will not be indexed, and the index value is less than that of the tablecount(*)Value, so in this case, the execution plan will naturally scan the whole table.

select * from test where id is not null;

Not condition

We know that when building an index, we create an entry for each index column. If the query condition is equivalent or range query, the index can find the corresponding entry according to the query condition.

On the other hand, when the query condition is non, it is difficult to locate the index. At this time, the execution plan may prefer full table scanning. Such query conditions include:<>、NOT、in、not exists

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

Like wildcard

When using fuzzy search, try to use post wildcards, such as name | ‘%’, because when searching the index, it will match the index column from the front. At this time, it can be found. If you use pre matching, it will be very troublesome to check the index. For example, if you query all people surnamed Zhang, you can search ‘Zhang%’.

On the contrary, if you check all the people called Ming, it can only be% Ming. How to locate the index at this time? In the case of pre matching, the execution plan will prefer to select full table scanning. Index range scan can be used after matching.

Therefore, in business design, we should try to consider the problem of fuzzy search and use more post wildcards.

Select * from test where name like Zhang | '%';

Conditionally include functions

In terms of query conditions, try not to use functions for index columns, such as the following SQL

select * from test where upper(name)='SUNYANG';

In this way, the index will not be used, because the index may be different from that after calculation, so it cannot be located to the index. However, if the query condition is not to calculate the index column, the index can still be used. such as

select * from test where name=upper('sunyang');

Such functions include: to_ char、to_ date、to_ Number, TRUNC, etc. Search official accountMigrant workers’ road to technology, reply “1024” and send you a technical resource. You can also check more technical dry goods articles and selected interview questions.

The leading columns of the composite index are large

When the differentiation of the leading columns of the composite index is small, we have index skip scan. The discrimination of the current leading column is large, and when the leading column is checked, the splitting of the leading column will consume a lot of resources. The execution plan is not as fast as the full table scan, and then the index fails.

select * from test where owner='sunyang';

Conversion of data types

When the query condition has implicit conversion, the index will become invalid. For example, the number type of ID stored in the database, but in the query, the following form is used:

select * from sunyang where id='123';

Connect By Level

When using connect by level, the index is not used.

  • Predicate operation

As we said above, we cannot perform functional operations on index columns, which also includes predicate operations of addition, subtraction, multiplication and division, which will also invalidate the index. Create a sunyang table with the index ID. look at the SQL:

select * from sunyang where id/2=:type_id;

It is obvious that the index column ID is divided by ‘/ 2’, and the index will become invalid. This situation should be rewritten as follows:

select * from sunyang where id=:type_id*2;

You can use the index. recommend:From understanding index to understanding index “index optimization”

Vistual Index

First of all, whether the establishment of a virtual index is useful depends on the specific implementation plan. If it works, you can build one. If it doesn’t work, it’s OK.

The general index is built as follows:

create index idx_test_id on test(id);

The virtual index is created as follows:

create index idx_test_id on test(id) nosegment;

Do an experiment. First create a table:

CREATE TABLE test_1116( 
id number, 
a number 

CREATE INDEX idx_test_1116_id on test_1116(id); 
CREATE INDEX idx_test_1116_a on test_1116(a)nosegment; 

Where id is the ordinary index and a is the virtual index.

Insert 100000 pieces of data into the table

for i in 1 .. 100000 loop 
        insert into test_1116 values (i,i); 
end loop; 

Then execute the following SQL to see the time. Due to the experiment on the intranet machine, the map cannot be pasted, and the authenticity of the data is guaranteed.

select count(id) from test_1116;
--First time: 0.061 seconds
--The second time: 0.016 seconds
select count(a) from test_1116; 
--First time: 031.0 seconds
--The second time: 0.016 seconds

Because Oracle caches the result set after one execution, the second execution takes the same time without index and memory. You can see that in this case, the virtual index is twice as fast as the ordinary index.

The use details of specific virtual indexes will not be discussed here.

Invisible Index

Invisible index is a new function provided by Oracle 11g. It is not visible to the optimizer (is it still connected to the CBO mentioned in the previous blog). I think this function is mainly used for testing. If there are so many indexes on a table, it will be very slow to check the execution plan one by one. At this time, it is better to build an invisible index that has no impact on tables and queries for debugging.

Operate the index with the following statement

alter index idx_test_id invisible;
alter index idx_test_id visible;

If you want CBO to see the invisible index, you need to add this sentence:

alter session set optimizer_use_invisible_indexes = true;

Basically that’s all. If you have any questions, please leave a message and make common progress!

Author: tomato has a fever

What are the scenarios of index invalidation? When will the index expire?