Scenario
An SQL statement has multiple indexes, but the index selected by the plan does not achieve optimal performance. Possible causes:
- The index specified in the SQL hint leads to poor performance or does not exist.
- An inappropriate index is bound to the SQL outline.
- The data distribution of input parameters used during hard parsing of the SQL statement is applicable to a minority of scenarios. As a result, an incorrect execution path is selected.
- The plan generated based on the data distribution during SQL tuning is not suitable for the data distribution after the business runs for a period of time.
Case analysis
The index bound to the SQL statement does not exist, as shown in the following diagnostic information:
The following figure shows the index information, which indicates that the ineffective_hint hint contains only the primary key but not an index named indexA.
Optimization suggestions
Cancel the index specified in the SQL hint so that the optimizer automatically selects an index.