A SQL tuning technique that is easy to be ignored-whether the order by field should be indexed or not

For SQL tuning, tune to the extreme. Editor is not a Virgo, but because in a business system with large concurrency, the improvement of the performance of a single SQL that is frequently executed may be of great significance to the performance improvement of the overall database.

But encounter the field behind the order by field, especially when this field is not in the filter condition, the editor will be excited about whether to add it to the index or not, will it not improve the performance, but will it make the index more complex and bring unnecessary additional burden to the system? it's a joke. But if you ignore this problem directly, it is very likely that this opportunity to improve system performance will be missed.

So today, the editor will discuss with you whether or not to join the index in the face of the condition behind the order by field, especially when this condition is not in the filter condition. For SQL tuning this account, adding the order by field to the index will make or lose ❓.

Part 1

Without much empty talk, let's start with a little experiment to warm up. By copying the data in dba_objects several times, the test table T1 is generated, with about 10 million rows of data. Do a simple query to query the smallest 10 rows of object_id in the T1 table, select * from (select * from T1 order by object_id) where rownum