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
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
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.
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.
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 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!