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

PS: I didn't bother to edit the picture again. I just copied me from the log on 51.

Background:

Today, I received a phone call from the project manager of a project and told me that there are several super slow queries in the production environment, that is, to query the data of a single table, and the query conditions are also very simple, but after adding the index, I did not take the index. I still scan the whole table.

Hearing the description of the problem, I began to wonder, is the statistics too old? Is there an implicit conversion? The inclination of the index tree is so high that oracle thinks it is more expensive to walk the index.

With all kinds of possible reasons, I rushed to the scene and found that I was thinking too much. Do not take the index simply is the establishment of the index is unreasonable, the query condition is multiple fields, we should establish a compound index, on-site maintenance personnel only set up an index on a single field, ORACLE thinks that it is better to scan the whole table with less overhead, so it does not take the index.

Sweat ~

When adding the index, I found that for several different queries, the query condition fields are all the same, but the writing order is different (the development and writing SQL is too casual! The field is the same, the order is also the same! ), the result is a spoiler, it is also possible to use the index. As a result, I think of some problems, so after solving the problem of efficiency, I do a verification in the personal environment.

This is the complete background.

Verify the content:

Conditions for the use of the ORACLE 11GR2 composite index.

Prerequisites:

Create a table and create a combined index of fields An and B in the order of index (A, B)

Test scenario:

Test against the following six scenarios:

1. The query condition is: identical XXX' and BackgroundYYYY; if the order is exactly the same

2. The query condition is: incorrect YYYY' and abnormal XXX. if the order is inconsistent

3. The query condition is: a single field and a leading column of a composite index

4. The query condition is: single field and not the leading column of the composite index

5. The query condition is: centering ZZZ' and leading column of composite index.

6. The query condition is: centering ZZZ' and Bounding columns YYYY; the query condition contains not only other fields, but also non-leading column fields of the composite index.

7. The query condition is: centering ZZZ' and Aids XXX 'and bounded fields YYYY; the query condition includes other fields except the index field, and the field outside the index is in the first place.

Execution process:

Create test data:

Create table test_index_demo (recid RAW (16) not null,customer_id RAW (16) not null,product_id RAW (16) not null)

Create unique index index_test on test_index_demo (CUSTOMER_ID, PRODUCT_ID)

Nearly 30 million data has been prepared by Cartesian product according to the query field.

Exec dbms_stats.gather_index_stats (ownname = > 'gboss',indname = >' test_index_demo')

Test each scenario separately, and check the execution plan as follows:

Scenario 1:

Select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW ('01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW (' 01540EAB83EC6900E30EAD424661D792')

This is indexed, as expected.

Scenario 2:

Select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW ('01540EAB83EC6900E30EAD424661D792') AND T.CUSTOMER_ID=HEXTORAW (' 01540EAAA1D8E690099261D41257815D9')

Here you can see that ORACLE's optimizer actually adjusts the order of the query conditions, so it also goes to the index, as expected.

Scenario 3:

Select * from TEST_INDEX_DEMO T WHERE T.CUSTOMER_ID=HEXTORAW ('01540EAA1D8E690099261D41257815D9')

Here, because the query condition is the leading column of the composite index, the index is gone, which is in line with the expectation.

Scenario 4:

Select * from TEST_INDEX_DEMO T WHERE T.PRODUCT_ID=HEXTORAW ('01540EAB83EC6900E30EAD424661D792')

There is no index here, a full table scan. I thought that even if it was not the leading column of the composite index, it would go to the index. It seems that my original understanding is wrong.

Needle on this I specially added index (B, A) to verify, because the query condition field is not caused by the composite index leading column, or because of other aspects of cost considerations, the test found that after adding the index (B, A) order index, the SQL can walk the index, so it should be considered that the query condition field is not caused by the composite index leading column.

Scenario 5:

Select * from TEST_INDEX_DEMO T WHERE

T.RECID=HEXTORAW ('01540ED5DA7069465FB7E42D07EDC156') AND T.CUSTOMER_ID=HEXTORAW (' 01540EAA1D8E690099261D41257815D9')

The ORACLE optimizer did not adjust the order of the query condition fields, but still walked out of the index. Walking the index is in line with expectations, but I should have changed the order of the fields in the future. I don't quite understand this part. If you have read this log, please help me solve the problem. Thank you.

Scenario 6:

Select * from TEST_INDEX_DEMO T WHERE

T.RECID=HEXTORAW ('01540ED5DA7A69461D878CAE1CED2B7E') AND T.PRODUCT_ID=HEXTORAW (' 01540EAB83EC6900E30EAD424661D792')

There is no index here, but it is in line with expectations.

Scene 7:

Select * from TEST_INDEX_DEMO T WHERE

T.RECID=HEXTORAW ('01540ED5DA7A69461D878CAE1CED2B7E') AND T.CUSTOMER_ID=HEXTORAW (' 01540EAA1D8E690099261D41257815D9') AND T.PRODUCT_ID=HEXTORAW ('01540EAB83EC6900E30EAD424661D792')

The index is here, and the results are in line with expectations.

Results comparison and conclusion:

So by comparison, my conclusion is:

1. If the query condition field is consistent with the composite index field, regardless of the order, the ORACLE optimizer will automatically adjust the order, and the conclusion is that the index will go.

2. The query condition field is not consistent with the composite index field. If the query condition field contains the leading column of the composite index, you can walk the index; if you do not include the leading column of the index, you will not walk the index.

-

Therefore, from this conclusion, we can see that when designing a query, we should still require the developer to design it by comprehensively considering all the queries in the system when organizing the SQL.

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.