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;
Copy code

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

NULL value condition exists

When designing database tables, we should try our best to avoid the occurrence of NULL values. If we have to avoid the occurrence of NULL values, we should also give a DEFAULT value. Numerical types can be given to 0, - 1 and so on. Sometimes there is a problem with empty strings, we should give a space or other. If the index column is nullable, it will not be indexed. The index value is less than the count(*) value of the table. In this case, the execution plan will naturally scan the whole table.

select * from test where id is not null;
Copy code

NOT condition

We know that when building an index, we create an entry for each index column. If the query criteria are equivalent or range query, the index can find the corresponding entry according to the query criteria. Conversely, when the query condition is non, index positioning is difficult. At this time, the execution plan may prefer full table scanning. Such query conditions include: < >, NOT, in and 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);
Copy code

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 troublesome to check the index. For example, if you query all people surnamed Zhang, you can search 'Zhang%'.

On the contrary, if you query 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, try to consider the problem of fuzzy search and use more post wildcards.

select * from test where name like Zhang||'%';
Copy code

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';
Copy code

In this way, the index will not be used, because the index may be different from that after calculation, and 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');
--INDEX RANGE SCAN
 Copy code

Such functions include: to_char,to_date,to_number, trunc, etc

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 differentiation of the current leading columns is large, and the splitting of the leading columns will consume a lot of resources when checking the later leading columns. The execution plan is not as fast as the full table scan, and then the index fails.

select * from test where owner='sunyang';
Copy code

Conversion of data types

When the query condition has implicit conversion, the index will be invalidated.

For example, the number type of id stored in the database, but the following form is used during query:

select * from sunyang where id='123';
Copy code

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;
Copy code

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

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

You can use the index.

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 create one. If it doesn't work, it's OK. The general index is built as follows:

create index idx_test_id on test(id);
Copy code

The virtual index is created as follows:

create index idx_test_id on test(id) nosegment;
Copy code

An experiment was conducted. First, a table was created:

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; 
Copy code

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

Insert 100000 pieces of data into the table

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

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

select count(id) from test_1116;
--First time: 0.061 second
--Second time: 0.016 second
select count(a) from test_1116; 
--First time: 0.031 second
--Second time: 0.016 second
 Copy code

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 normal 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 and MySQL also has it. I think this function is mainly used for testing. If there are so many indexes on a table, it will be very slow to see 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;
![image](/img/bVbMc2Z)
Copy code

If you want CBO to see the Invisible Index, you need to add this sentence:

alter session set optimizer_use_invisible_indexes = true;

I hope it can help you!

Friends who need to get free materials, welcome to add a little assistant: soxwv ； to get materials for free!