In-depth exposure of restrictions on Oracle index usage

When you query data from a data table, Oracle database provides two query methods. One is to read each row from the table, which is a full table scan that is commonly said; the other is to use ROWI

When you query data from a data table, Oracle database provides two query methods. One is to read each row from the table, which is a full table scan that is commonly said; the other is to use ROWI

As we all know, indexes can indeed improve database performance. However, the restrictions on the use of indexes are not mentioned. I think the function of database indexing should be divided into two aspects. In addition to affirming its positive impact on database performance, we also need to recognize its potential negative impact. Only in this way can the Database Administrator correctly use the correct index. You need to know that sometimes a wrong index may lead to deadlocks, resulting in a sharp decline in database performance or termination of the process. If the database administrator can make a correct judgment, so that those processes that would have been running for several hours or even a day can be completed within several minutes. Therefore, the two gaps are one in the sky and one in the ground. Therefore, the author hopes that this article will allow readers to understand the restrictions on the use of indexes and understand that indexes are not omnipotent.

I. The impact of indexes on database performance is directly linked to data selectivity.

When you query data from a data table, Oracle database provides two query methods. One is to read each row from the table, which is a common full table scan. The other is to read a row through ROWID. When there are many records in the table, it is obvious that the second method can locate the record content more quickly. The index is based on the query principle. For example, a table has more than 3 million records, but now you only need to know 10 of them. At this time, if you use the index to identify the read block, you can execute a relatively small number of I/O operations, and the database system will quickly find the content you need. If no index is used, all the blocks in the table need to be read.

If an index is added to this table, what is the impact on the database performance? This is hard to say, because it is related to many factors. For example, this is directly related to data selectivity. If the user's data is very selective, then the table's home function only has a few rows matching the index value, Oracle will be able to quickly query the indexes that match the cited ROWID, in addition, you can quickly query a small number of related tables. For example, in the preceding table, if all resident population information of a city is stored, the ID card number must be indispensable. If you want to query the information of a person based on the ID card number, the database can provide a response in a short time. This is mainly because the data provided by the user is very selective and basically corresponds to the index values in the database. However, if you want to query information based on the date and year of birth, the database will be slower.

It can be seen that the impact of indexes on database performance is directly linked to the data selectivity. This is enlightening for database administrators to design indexes. For example, when designing indexes, the database administrator should be able to select unique fields or fields with less repetition. In this case, indexes are of great value for database performance.

2. The index effect depends on the specific storage location recorded in the database.

In the preceding table, if you want to search for elders over 100 years old, send condolences to them. Suppose there are only 10 people who meet this condition. What is the impact of indexes on database performance? At this point, the data is obviously very selective, but not necessarily the index can play a very good effect. This depends on the storage location. If these 10 records are physically stored in the hard disk at a close location, if they may be within the same sector, then the index will have a greater impact on the database performance ,, data that meets the conditions can be found in the shortest time. However, if the locations of related rows in the table are not close to each other, the index effect will gradually decrease. If the data matching the index value is scattered across multiple hard disks, You must select multiple separate blocks from the table to meet the query requirements.

The database administrator should pay special attention to this. At this time, if the database administrator queries the index, it is likely to be superfluous. The author suggests that when the database administrator finds that data is scattered in multiple blocks of the table, it is best not to use indexes, but to perform full table scanning. In this case, scanning is more efficient than executing an index. During full table scan, the Oracle database system uses multiple reads to accelerate table scan. If an index is used, the data is read by a single block. Because the data is stored in multiple blocks, the reading speed is slower.

It can be seen that the Oracle database administrator should also find a way to store the data as close as possible in the database design and daily maintenance. Avoid deploying different application services on the same server as much as possible to prevent excessive disk fragments from the hard disk. If you need to use multiple hard disks, it is better to place similar tables in the same tablespace through the tablespace, so that the relevant rows are stored in zhognd as close as possible to improve the index usage. That is to say, when database administrators use indexes, creating indexes for fields in the table is only the first step in their work. In the subsequent database maintenance and adjustment process, pay attention to the impact of the data storage location on the index.