Oracle index Optimization Design

The Rational Use and Management of Oracle indexes can accelerate the efficiency of database queries. The following describes the optimization design of Oracle indexes in detail, hoping to enlighten you.

1. Manage organization Indexes

Oracle indexes can greatly speed up database queries. Oracle indexes map logical values in tables to secure rowids. Therefore, indexes can quickly locate physical data addresses. However, some DBAs find that the indexing of a large table does not improve the data query speed, but affects the performance of the entire database.

This is mainly related to the data management method of SGA. When ORACLE performs data block high-speed cache management, index data has higher resident permissions than normal data. During Space competition, ORACLE first removes normal data. When you query a large table with an index, index data may use up all the data block cache space. ORACLE has to perform disk read/write frequently to obtain data, therefore, after partitioning a large table, you can create a partition index based on the corresponding partition.

If the data in such a large table is frequently queried, or the index is not created. In addition, when creating an index, DBA should try to ensure that the index is most likely to be used in the where clause. If only one index is created for the query, it will not necessarily speed up, because the index must specify a suitable access path.

2. Use of clustering

Oracle provides another method to improve the query speed, that is, Clustering Cluster ). In simple words, clustering stores several tables together and stores them in a mixture of public attributes. The data of multiple tables is stored in the same Oracle block by the common code value of the cluster root data. Then, when a group of Oracle blocks is retrieved, the data of the two tables is obtained at the same time, in this way, the Oracle block to be stored can be reduced to improve the application performance.

3. Optimize the configured index to make full use of it to speed up database access. There are some basic conditions for ORACLE to use an index:

1) this field in the where subname must be the first field of the composite index;

2) This field in the where subname should not be involved in any form of calculation.

Oracle index type

Implementation of parallel query of one column in oracle

Use of oracle rownum statements

Oracle index type

How to create an Oracle Index