Although your column is indexed and the query condition is indexed, the final execution plan does not take its index.

Here are a few key points that cause this problem.

Compare columns to columns

If two columns (ID and c_id) in a table have separate indexes, the following query condition does not move the indexes

select * from test where id=c_id;
Copy the code

This would be considered worse than a full table scan.

A NULL value condition exists

When designing a database table, we should try to avoid NULL values. If NULL values are unavoidable, we should give a DEFAULT value, such as 0, -1, etc., string sometimes empty string problems, 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, so the execution plan will scan the entire table.

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

The NOT condition

We know that during index building, an entry is created 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 NOT, it is difficult to locate the index, and the execution plan may prefer to scan the entire table. The 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 the code

LIKE a wildcard

Rear when using fuzzy search, as far as possible use wildcards, for example: the name | | ‘%’, because the index, it will be to match from the index columns, can be found at this time, if the previous match, then check the index will be very troublesome, all zhang query, for example, you can go to search ‘%’.

On the other hand, if you query all people named Ming, it will only be % Ming. How does the index position at this point? In the case of pre-match, the execution plan is more inclined to select full table scan. After matching, you can go to INDEX RANGE SCAN.

So business design, as far as possible to consider the problem of fuzzy search, to use more post-wildcard characters.

Select * from test where the name like zhang | | '%'.Copy the code

The conditions include functions

Try not to use functions on indexed columns in query conditions, such as the following SQL

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

This will not go to the index, because the index in the creation and calculation may be different, unable to locate the index. However, if the query condition does not evaluate the index column, the index can still be moved. Such as

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

Other such functions are to_char, to_date, to_number, trunc, and so on

Compound index leading column differentiation is large

When the leading column distinction of composite INDEX is small, we have INDEX SKIP SCAN. When the leading column distinction of the current INDEX is large and the leading column is searched after, the splitting of the leading column will consume resources very much. The execution plan expects that the SCAN of the whole table is not as fast as the SCAN of the whole table, and then the INDEX fails.

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

Conversion of data types

Indexes are invalidated when there is an implicit conversion of query criteria.

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

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

Connect By Level

When connect by level is used, indexes are not moved.

Predicate evaluation

We said above that we cannot perform functional operations on index columns, including predicate operations on addition, subtraction, multiplication and division, which also invalidates the index.

Select * from sunyang where id = 1;

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

Alter table id = ‘/2’; alter table id = ‘/2’;

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

You can use the index.

Vistual Index

To clarify, the creation of a virtual index depends on the implementation plan. If it works, you can create one. If it doesn’t work, you can forget it. Normal indexes are built like this:

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

Virtual Index Vistual Index

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

We did 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; 
Copy the code

Id is the common index, and A is the virtual index.

Insert 100,000 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 the code

Then respectively to execute the following SQL to see the time, because in the Intranet machine to do the experiment, the picture is not posted, data to ensure authenticity.

select count(id) from test_1116; Select count(a) from test_1116; select count(a) from test_1116; The first operation takes 0.031 seconds. The second operation takes 0.016 secondsCopy the code

Because oracle caches the result set after the first execution, the second execution takes the same amount of time without using the index.

You can see that in this case, the virtual index is twice as fast as a normal index.

The use of specific virtual indexes is not discussed here.

Invisible Index

Invisible Index is a new function provided by Oracle 11g. It is Invisible to the optimizer, and it is also available in MySQL. I feel that this function is mainly used for testing. At this time it is better to build a table and query Invisible Index has no impact on the debugging, it is very good.

The index is manipulated by the following statement

alter index idx_test_id invisible; alter index idx_test_id visible; ! [image](/img/bVbMc2Z)Copy the code

If you want the CBO to see Invisible Index, add the following:

alter session set optimizer_use_invisible_indexes = true;
Copy the code