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 table
count(*)Value, so in this case, the execution plan will naturally scan the whole table.
select * from test where id is not null;
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:
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);
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'); --INDEX RANGE SCAN
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”
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
begin for i in 1 .. 100000 loop insert into test_1116 values (i,i); end loop; commit; end;
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 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