Oracle performance optimization operation 8: Use HINT to forcibly specify the index, oraclehint

Oracle performance optimization operation 8: Use HINT to forcibly specify the index, oraclehint

When the ORACLE optimizer cannot use a reasonable index, the HINT is used to forcibly specify the index.

In the example of "oracle Performance Optimization Operation 7", ORACLE determines by default that the values in the columns in the table are evenly distributed across all data rows,

That is to say, under 1 million of the data volume, each DisposalCourseFlag value corresponds to 0.125 million data rows.

Assume that the SQL search condition is DisposalCourseFlag = 2, and use the index on the DisposalCourseFlag column for data search efficiency,

ORACLE often does not scan the entire table, so it turns a blind eye to the index. In this way, you can use indexes on other fields or even scan the entire table in the selection of the query path.

According to the above analysis, the data value distribution is very special, and the distribution is serious uneven. To improve efficiency by using indexes,

On the one hand, you can analyze this field or the table with the analyze statement separately, and collect sufficient statistical data for this column, so that ORACLE can use indexes when querying highly selective values;

On the other hand, you can use the HINT prompt to add "/* + INDEX (Table Name, INDEX name) */" after the SELECT keyword to force the ORACLE optimizer to use this INDEX.

For example:

Select * from serviceinfo where DisposalCourseFlag = 1;

In the preceding statement, ORACLE uses full table scan in actual execution. After the HINT part, index query is used. As follows:

Select/* + INDEX (SERVICEINFO, IX_S_DISPOSALCOURSEFLAG) */* from serviceinfo where DisposalCourseFlag = 1;

Note that this method will increase the difficulty of code maintenance. After the index name on this field is changed, you must synchronize all the HINT codes of the specified index,

Otherwise, the HINT prompt will be ignored by ORACLE.