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

[Enmo College] the impact of more than 20 indexes on tables in remote databases

He Jianmin

My colleague attended a seminar yesterday and a case was mentioned. A remote database query through dblink, the original query is very fast, but after the remote database added an index, the query suddenly slowed down.

After analysis, it is found that the query statement through dblink, when querying the remote database, uses the index, but after the remote database adds the index, if the number of indexes exceeds 20, the first index will be ignored. If the query statement happens to use the first index, it can only go Full Table Scan after being ignored.

After listening to this case, I checked that in the official document of the oracle database, there is a passage about Managing a Distributed Database:

Several performance restrictions relate to access of remote objects:

Remote views do not have statistical data.

Queries on partitioned tables may not be optimized.

No more than 20 indexes are considered for a remote table.

No more than 20 columns are used for a composite index.

It is said that if the remote database uses more than 20 indexes, these indexes will not be considered. This paragraph has existed in the documents since oracle 9i until 12.2.

So, with more than 20 indexes, are the new indexes ignored? Or is the old index ignored? How to make ignored indexes aware of oracle databases? Let's test it.

One initialization test table

As you can see, the remote table has 27 fields, and so far only the first 20 fields have been indexed, and the first field is the primary key. There are 6 fields in this table, and all 6 fields are indexed.

Second, in the first round of testing, there are 20 indexes on the remote table.

Test scenario 1:

In the case of remote table 20 index, the local table is associated with the remote table, and the first field of the remote table is associated with the first field of the local table:

We can see that for the execution plan of the remote table, this is the primary key.

Test scenario 2:

In the case of remote table 20 index, the local table is associated with the remote table, and the 20th field of the remote table is associated with the first field of the local table:

We can see that for the execution plan of the remote table, this is scanned by the index range.

Test scenario 3:

In the case of remote table 20 index, the local table is associated with the remote table, and the second field of the remote table is associated with the second field of the local table:

We can see that for the execution plan of the remote table, this is scanned by the index range.

Test scenario 4:

In the case of remote table 20 index, the local table is associated with the remote table, and the 20th field of the remote table is associated with the second field of the local table:

We can see that for the execution plan of the remote table, this is scanned by the index range.

3. Establish the 21st index

Four repeat the above four tests

Test scenario 1:

We can see that for the execution plan of the remote table, if the association condition is the first field of the remote table, the index on the first field is ignored, and the execution plan selects a full table scan.

Test scenario 2:

We can see that for the execution plan of the remote table, if the association condition is the 20th field of the remote table, the index on the 20th field is not ignored, and the execution plan is to follow the index.

Test scenario 3:

We can see that for the execution plan of the remote table, if the association condition is the second field of the remote table, the index on this second field is not ignored, and the execution plan is to take the index.

Test scenario 4:

We can see that for the execution plan of the remote table, if the association condition is the 20th field of the remote table, the index on the 20th field is not ignored, and the execution plan is to follow the index.

We can now conclude that when the 21st index of the remote table is established, the local table and the remote table are associated through dblink. If the association condition is the field of the first index of the remote table, then the index will be ignored and the full table scan will be performed. If the association condition is the second indexed field of the remote table, it is not affected.

The window that seems to be a valid index is 20, and when the 21st is created, the first is ignored.

Fifth, set up the 22nd index to verify the above conjecture

Repeat the above four tests again

Test scenario 1:

Test scenario 2:

Test scenario 3:

Test scenario 4:

The above tests can actually verify our conjecture. The oracle database is only aware of the fields of the most recently created 20 indexes for accessing remote tables through dblink associations. This realizes that there are 20 windows for the index, and once a new index is created, the oldest index will be ignored.

Seven try rebuild index

Second index of rebuild

Eight repeat the above test

So we can see that the index rebuild cannot play the role of "waking up" the index again.

Nine try drop and recreate the second index

Ten repeat the above tests 3 and 4

We can see that the second index can be "awakened" by rebuilding after drop. This also proves that the mobile windows identified by our 20 indexes are moved according to the creation time of the index.

To sum up:

1. For associating local tables and remote tables through dblink, if the number of indexes of the remote table is less than 20, then it is not affected.

two。 For associating local tables and remote tables through dblink, if the number of indexes of remote tables increases to 21 or more, then the oracle database will ignore the earliest created index when performing remote operations, but will move in a window of 20, and the newly created index will be recognized. At this point, if the field of the earliest created index is used in the association condition of the query, the full table scan will be performed because the index is ignored.

3. To "awaken" awareness of the original index, the rebuild index is invalid and requires a drop & create index.

4. When the amount of data on the local surface is relatively small, the amount of data in the remote table is very large, and the number of indexes is more than 20, and the field of the associated condition is the earliest index, you can consider using the hint of DRIVING_SITE to send all the data of the local surface to the remote. At this time, the remote associated query can be aware of that index. We can see the example at the end of the article. Whether or not to use hint, you need to assess the cost of fully pushing local surface data to the remote, and the cost of using full table sweeps for remote tables.

Attachment: in the case of 22 indexes, try to use DRIVING_SITE 's hint:

Enmo College, a subsidiary of Yunhe Enmo (Beijing) Information Technology Co., Ltd., is committed to providing professional and high-level oracle database and big data training services, mining and training big data and database talents. Enmo College provides a full range of big data and database technology training, including individual practical skills training, personal certification training, and enterprise internal training. ACE-level super teachers, equipped with professional laboratories, immersion learning and training, professional laboratories, equipped with professional teaching assistants to guide training. Can quickly integrate into the circle of experts, rich in resources in the industry, and quickly accumulate workplace contacts. Oracle database courses include: Oracle DBA practical class, Oracle OCM examination, Oracle OCP examination and so on.

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.