If the query includes an order by clause, additional messages are displayed. The optimizer checks to see if an index matches the ordering required by the order by clause, to avoid incurring sort costs for the query.
This message is printed for search clauses:
Selecting best index for the SEARCH SORTAVERT CLAUSE:
titles.type = ’business’
The message for joins shows the column under consideration first. This message is printed while the optimizer analyzes the titles table:
Selecting best index for the JOIN SORTAVERT CLAUSE:
titles.title_id = titleauthor.title_id
At the end of the block for the search or join clause, one of two messages is printed, depending on whether an index exists that can be used to avoid performing a sort step. If no index is available, this message is printed:
No sort avert index has been found for table ’titles’ (objectid 208003772, varno = 0).
If an index can be used to avoid the sort step, the sort-avert message includes the index ID, the number of pages that need to be accessed, and the number of rows to be returned for each scan. This is a typical message:
The best sort-avert index is index 3, costing 9 pages and generating 8 rows per scan.
This message does not mean that the optimizer has decided to use this index. It means simply that, if this index is used, it does not require a sort.
If you expect an index to be used to avoid a sort, and you see the “No sort avert index” message, check the order by clauses in the query for the use of asc and desc to request ascending and descending ordering, and check the ordering specifications for the index.
For more information, see “Costing for queries using order by” on page 77 in Performance and Tuning: Optimizer.
