One of the practical skills that can greatly improve the performance of SQL TUNING optimization.

One of the practical skills that can greatly improve the performance of SQL TUNING optimization.

When we perform SQL optimization, we often encounter the need to sort a large number of datasets and then retrieve the first part of the results from the sorted set. In this case, when we write SQL statements according to the general idea, the system first reads and filters all sets, then sorts them, and then extracts a very small number of results from the sorting results. In this process, scanning, reading, filtering, and sorting of a large amount of data will consume a large amount of system resources, and SQL Performance will also have great problems. In practice, it is common that results are not returned within several minutes or even several hours. To optimize the SQL statements in this scenario, we often scan the index created on the sorting column in the query order, avoiding reading and sorting a large amount of data.

However, in practice, it is found that when the index column does not appear in the condition, ORACLE will not generate a plan to scan the index, that is, using hint cannot make the query scan along the target index. For example:

Create table t1 (c1 int, c2 char (10 ));

Create index idx1_t1 on t1 (c1 );

Select * from (

Select * from t1 order by c1)

Where rownum <6;

Obviously, this write method will read the table first, sort the table, and then take the first five records. The plan is as follows:

If we write this statement, the semantics is the same, but it saves a lot of reading and sorting costs:

Select/* + index (t1, idx1_t1) */* from t1 where rownum <6;

The SQL Execution plan is as follows:

We can see that the SQL statement does not scan the idx1_t1 index in sequence according to the hint instructions. Why? I guess it may be because the Oracle optimizer thinks that there is no c1 column in the filter condition, and the efficiency of indexing is lower than that of the FTS. Therefore, we simply do not consider taking the index plan, that is, the hint should also be ignored, which is not satisfactory. So, how can we choose the optimizer to scan idx1_t1? We only need to add a condition for the c1 column in The where clause. For example:

Select/* + index (t1, idx1_t1) */* from t1 where rownum <6 and c1 <>-1;

The modified plan is as follows:

It can be seen that when we optimize similar scenarios, we only need to meet two requirements:

1. An index exists in the sorting column;

2. The where condition contains the condition on the index column;

If index-based scanning can be implemented, it is very likely that the performance will be improved by thousands of times, which has been verified in practice.