Shulou(Shulou.com)06/03 Report--

This article mainly introduces "what are the cases of oracle index failure". In the daily operation, I believe that many people have doubts about the situation of oracle index failure. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what is the case of oracle index failure?" Next, please follow the editor to study!

Column to column comparison

In a table, two columns (id and c_id) are indexed separately, and the following query condition does not leave the index.

Select * from test where id=c_id

This situation will be considered to be better than a full table scan.

Null condition exists

When designing database tables, we should try our best to avoid null values. If we have to have null values inevitably, we should also give a default value. Numeric values can be given to 0,-1 and so on. Sometimes, if there is a problem with an empty string, give a space or something else. If the index column is nullable, it will not be indexed, and the index value is less than the count (*) value of the table, so in this case, the execution plan naturally scans the whole table.

Select * from test where id is not null;NOT condition

We know that when building an index, 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 no, it is difficult to locate the index, and the execution plan may be more inclined to full table scan at this time. Such query conditions include:, NOT, in, not exists

Select * from test where id500;select * from test where id in; select * from test where not in; select * from test where not exists (select 1 from test_02 where test_02.id=test.id); LIKE wildcards

When using fuzzy search, try to use post-wildcards, such as: name | |'%', because when walking through the index, it will match the index column from the front, which can be found at this time. If you use pre-matching, it will be very troublesome to look up the index. For example, if you query all people surnamed 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 a front match, the execution plan is more likely to choose a full table scan. After the Standard PvP match, 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 Zhang | |'%'; include function on condition

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

Select * from test where upper (name) = 'SUNYANG'

This will not go to the index, because the index may be different when it is built and after calculation, and the index cannot be located. However, if the query condition is not calculated on the index column, then you can still go to the index. such as

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

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

The leading column of the composite index distinguishes large.

When the leading column differentiation of the composite index is small, we have INDEX SKIP SCAN, and when we look up the leading column, the splitting of the leading column will be very resource-consuming, and the execution plan is not as fast as that of the whole table scan, and then the index is invalid.

Conversion of select * from test where owner='sunyang'; data types

When there is an implicit conversion of the query condition, the index is invalidated.

For example, the number type stored in id in the database, but when querying, it uses the following form:

Select * from sunyang where id='123';Connect By Level

When using connect by level, the index is not taken.

Predicate operation

As we said above, you cannot perform a functional operation on an index column, which also includes the predicate operation of addition, subtraction, multiplication and division, which also invalidates the index.

Create a sunyang table with an index of id. Look at this SQL:

Select * from sunyang where id/2=:type_id

It is obvious that the index column id is divided by two by'/ 2', which will cause the index to fail, which should be rewritten as follows:

Select * from sunyang where id=:type_id*2

You can use the index.

Vistual Index

First of all, to explain whether the establishment of the virtual index is useful, it depends on the specific implementation plan. If it works, you can build one, and 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 as follows:

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 general index and an is a virtual index.

Insert 100,000 pieces of data into the table

Begin for i in 1.. 100000 loop insert into test_1116 values (iQuery I); end loop; commit; end

Then go to the following SQL to see the time, because the intranet machine does the experiment, the picture can not be posted, and the data is guaranteed to be authentic.

Select count (id) from test_1116;-- first time: 0.061 seconds-second time: 0.016 seconds select count (a) from test_1116;-- first time: 0.031 seconds-second time: 0.016 seconds

Because oracle caches the result set after one execution, the second execution takes time without moving the index, and the memory is the same.

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

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

Invisible Index

Invisible Index is a new feature provided by oracle 11g, which is invisible to the optimizer and also has MySQL. I feel that this function is more important for testing. If there are so many indexes on a table, it will be very slow to debug one by one to see the execution plan. At this time, it would be better to build an Invisible Index that has no impact on the table and query to debug.

Manipulate the index through the following statement

Alter index idx_test_id invisible;alter index idx_test_id visible! [image] (/ img/bVbMc2Z)

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

Alter session set optimizer_use_invisible_indexes = true; at this point, the study of "what is the failure of the oracle index?" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.