17.5.14 Types of ORDER BY processing methods

Organization of this subsection
(1) HiRDB/Single Server
(2) HiRDB/Parallel Server

(1) HiRDB/Single Server

The following processing methods are available to a HiRDB/Single Server:

SORT CANCEL BY INDEX[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which the columns subject to ORDER BY can be sorted by retrieving their index.
If a function for obtaining the first n rows of a search result is used, the utility may display (LIMIT SCAN). In such a case, the utility searches only the first n rows of the index and obtains the first n rows of the search result.
SORT CANCEL BY JOIN
This method skips the sorting for ORDER BY in situations in which all columns subject to ORDER BY are specified in the join condition because merge-join sorting takes place.
SORT CANCEL BY DISTINCT[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which all the value expressions subject to ORDER BY are sorted by DISTINCT processing.
If a function for obtaining the first n rows of a search result is used, the utility may display (LIMIT SCAN). In such a case, the utility obtains the first n rows of the DISTINCT processing result.
SORT CANCEL BY GROUPING[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which all the columns subject to ORDER BY can be sorted by grouping.
If a function for obtaining the first n rows of search result is used, the utility may display (LIMIT SCAN). In such a case, the utility obtains the first n rows of the grouping processing result.
LIST SORT
This method obtains the result of ORDER BY by creating a work table and sorting it.
LIMIT SCAN
The utility may display this if a function for obtaining the first n rows of the search result is used. In such a case, the utility obtains the first n rows of the ORDER BY result by sorting only a limited number of rows, not all the rows that satisfy the search condition.
PRE-SORT JOIN
In situations in which all the columns or value expressions subject to ORDER BY are included in an outer table before it is joined or in an outer-joined table, this method executes sorting before the join operation, executes the join operation in the sorted order, then obtains the result of ORDER BY.
PRE-SORT SET OPERATION[(LIMIT SCAN)]
This method executes a sort operation for each query prior to a set operation to use the sorted order for operations. The method then obtains the result of ORDER BY and set operation.
If a function for obtaining the first n rows of the search result is used, the utility may display (LIMIT SCAN). In such a case, the utility performs the set operation until it obtains the first n rows and then obtains the first n rows of the result of the ORDER BY and the set operation.
SORT CANCEL FOR SAME VALUES
This method omits sort processing by ORDER BY when ORDER BY's target value expressions are all guaranteed to be the result of window functions and have the same value in all rows.

(2) HiRDB/Parallel Server

The following processing methods are available to a HiRDB/Parallel Server:

SORT CANCEL BY INDEX[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which the columns subject to ORDER BY can be sorted by retrieving their index.
If a function for obtaining the first n rows of the search result is used, the utility may display (LIMIT SCAN). In such a case, the utility searches only the first n rows of the index and obtains the first n rows of the search result.
SORT CANCEL BY DISTINCT[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which all the value expressions subject to ORDER BY are sorted by DISTINCT processing.
If a function for obtaining the first n rows of the search result is used, the utility may display (LIMIT SCAN). In such a case, the utility obtains the first n rows of the DISTINCT processing result.
SORT CANCEL BY GROUPING[(LIMIT SCAN)]
This method skips the sorting for ORDER BY in situations in which all the columns subject to ORDER BY can be sorted by grouping.
If a function for obtaining the first n rows of the search result is used, the utility may display (LIMIT SCAN). In such a case, the utility obtains the first n rows of the grouping processing result.
LIST SORT
This method obtains the result of ORDER BY by creating a work table and sorting it at each back-end server.
LIMIT SCAN
The utility may display this if a function for obtaining the first n rows of the search result is used. In such a case, the utility obtains the first n rows of the ORDER BY result by sorting only a limited number of rows, not all the rows that satisfy the search condition.
PRE-SORT JOIN
In situations in which all the columns or value expressions subject to ORDER BY are included in an outer table before it is joined or in an outer-joined table, this method executes sorting before join operation, executes join operation in the sorted order, then obtains the result of ORDER BY.
FLOATABLE SORT
This method obtains the result of ORDER BY by transferring data to multiple floating servers, creating a work table, and sorting the data in it.
LIST SORT WITH SET OPERATION[(LIMIT SCAN)]
This method executes the sorting for ORDER BY during a set operation without executing a sort operation for each query. The method then executes operation in the sorted order to obtain the result of ORDER BY and set operation.
If a function for obtaining the first n rows of the search result is used, the utility may display (LIMIT SCAN). In such a case, the utility performs the set operation until it obtains the first n rows and then obtains the first n rows of the result of the ORDER BY and the set operation.
SORT CANCEL FOR SAME VALUES
This method omits sort processing by ORDER BY when ORDER BY's target value expressions are all guaranteed to be the result of window functions and have the same value in all rows.