What are the scenarios where the index fails? When will the index become invalid?

Although you have built an index on this column, the query condition is also an index column, but the final execution plan does not take its index. The following are a few key points that cause this problem.

Column and column comparison

In a table, there are two columns (id and c_id) have a separate index, the following query conditions will not take the index

select * from test where id=c_id;

In this case, it would be considered inferior to a full table scan.

There is a NULL value condition

When designing database tables, we should try our best to avoid NULL values. If NULL values are unavoidable, we must also give a DEFAULT value. Numeric values can be 0, -1 and the like. Strings are sometimes empty. If there is a problem with the string, give a space or other.

If the index column is nullable, it is likely that it will not be indexed. The index value is less than the count(*) table, so in this case, the execution plan will naturally scan the entire table.

select * from test where id is not null;

NOT condition

We know that when creating an index, an entry is created for each index column. If the query condition is equal or range query, the index can find the corresponding entry according to the query condition.

<>、NOT、in、not exists , index positioning is difficult. The execution plan may be more inclined to a full table scan at this time. Such query conditions are:

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 the wildcard at the back, for example: name||'%', because when indexing, it will match the index column before, which can be found at this time. If the previous match is used, then check the index It will be very troublesome. For example, to search for all the people whose surname is Zhang, you can search for'Zhang%'.

On the contrary, if you query all the people named'Ming', it can only be %Ming. How to locate the index at this time? In the case of previous matching, the execution plan will be more inclined to choose a full table scan. After matching, you can go INDEX RANGE SCAN.

Therefore, when designing the business, try to consider the problem of fuzzy search and use more post-wildcards.

select * from test where name like 张||'%';

Conditionally include function

Try not to use functions for index columns in query conditions, such as the following SQL

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

In this way, the index will not be taken, because the index may be different when it is created and after the calculation, and the index cannot be located. But if the query condition is not to calculate the index column, then the index can still be used. such as

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

Such functions include: to_char, to_date, to_number, trunc, etc. Search the public number Migrant Workers Tech Road , reply "1024", give you a copy of technical resources, you can also read more technical dry goods articles and selected interview questions.

The leading column of the composite index is distinguished

When the leading column of a composite index is small, we have INDEX SKIP SCAN. The current leading column has a large distinction, and when the trailing column is checked, the splitting of the leading column will be very resource intensive. The execution plan is not as good as a full table scan. Fast, and then the index becomes invalid.

select * from test where owner='sunyang';

Data type conversion

When there is an implicit conversion in the query condition, the index will become invalid. For example, the number type stored in id 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 will not be used.

• Predicate operation

We said above that functional operations cannot be performed on indexed columns. This also includes the predicate operations of addition, subtraction, multiplication and division, which will also invalidate the index. Create a sunyang table, the index is id, look at this SQL:

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

Obviously, the index column id is divided by two by'/2'. At this time, the index will become invalid. This situation should be rewritten as:

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

You can use the index. Recommendation: From understanding index to understanding index "index optimization"

Vistual Index

First explain, whether the creation of a virtual index is useful, you need to look at the specific execution plan, if it works, you can build one, if it doesn't work, forget it.

The general index is built like this:

create index idx_test_id on test(id);

The virtual index Vistual Index is built like this:

create index idx_test_id on test(id) nosegment;

To 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 a normal index, and a is a virtual index.

Insert one hundred thousand pieces of data in the table

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

Then execute the following SQL to check the time. Because of the experiment on the internal network machine, the picture cannot be posted, and the data is guaranteed to be authentic.

select count(id) from test_1116;
--第一次耗时：0.061秒
--第二次耗时：0.016秒

select count(a) from test_1116; 
--第一次耗时：0.031秒
--第二次耗时：0.016秒

Because after executing it once, oracle caches the result set, so the second execution takes time without indexing, and memory is the same. It can be seen that in this case, the virtual index is twice as fast as the normal index.

The details of the specific virtual index will not be discussed here.

Invisible Index

Invisible Index is a new function provided by Oracle 11g, which is not visible to the optimizer (also received the CBO mentioned in the previous blog). I feel that this function is more for testing. If there are so many indexes on a table, one It is very slow to debug the execution plan. At this time, it is better to build an Invisible Index that has no effect on tables and queries for debugging, which is very good.

Operate the index through 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;

That's basically it. If you have any questions, please leave a message to point out and make progress together!

Author: Tomato has a fever
blog.csdn.net/bless2015/article/details/84134361