One of the practical techniques for improving performance when SQL tuning is optimized

When we are doing SQL optimization, we often encounter the need to sort a large number of datasets and then take the results from the pre-ordered collection, in which case, when we follow the usual thinking to write SQL, the system will read the filter to get all the collection, then sort, and then take out a very small number of results from the sorting results, the process, The scanning, filtering, sorting of large amounts of data consumes a large amount of system resources, and SQL performance can be problematic, and in practice, it is common to have no results in minutes or even hours. To optimize SQL for this scenario, we often let the query order scan the index built on the sorting sequence, bypassing a lot of data reading and sorting.

In practice, however, Oracle does not have a plan to scan the index when the index column does not appear in the condition, that is, using hint also does not allow the query to be scanned along the destination 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 writing will result in reading the table first, then sorting, and then taking the first 5 records, the plan is as follows:

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

Select/*+ Index (T1,IDX1_T1) */* from T1 where rownum<6;

The execution plan for this SQL is as follows:

As you can see, the SQL does not follow the hint instructions and sequentially scans the IDX1_T1 index, which is why? Personally, it may be that the Oracle optimizer thinks that the C1 column in the filter condition, the index is less efficient than the FTS, so it is a little unsatisfactory to simply ignore the index, that is, to use hint. So, what can we do to get the optimizer to choose a plan to scan idx1_t1? We just need to add a C1 column to the Where, for example:

Select/*+ Index (T1,IDX1_T1) */* from T1 where Rownum<6 and c1<>-1;

This revised plan is as follows:

This shows that when we optimize a similar scenario, we only need to meet two points:

1, there are indexes on the row sequence;

2, where condition has the condition on the index column;

It is also possible to achieve thousands of improvements in performance if you are able to scan by index, which is verified in practice.

One of the practical techniques for improving performance when SQL tuning is optimized