All of the ordering columns are evaluated to determine if they can be removed from the list of ordering columns. Only those ordering columns that have isolatable selection predicates with an equal operator specified can be considered. This guarantees that the column can only match a single value and will not affect the ordering. This processing is done to allow the optimizer to consider more indexes to implement the query and to reduce the number of columns that will be added as key columns to any temporary result used to implement the ordering.
The following example illustrates a query where the optimizer could eliminate an ordering column:
CREATE INDEX X1 ON EMPLOYEE (LASTNAME, WORKDEPT)
DECLARE DEPTEMP CURSOR FOR
SELECT EMPNO, LASTNAME, WORKDEPT
FROM CORPDATA.EMPLOYEE
WHERE EMPNO = '000190'
ORDER BY EMPNO, LASTNAME, WORKDEPT
In this example, the optimizer can remove EMPNO from the list of ordering columns because of the EMPNO = '000190' selection predicate. An index that has only LASTNAME and WORKDEPT specified as key columns can be used to implement the query. If a temporary result is required, then it is possible EMPNO may not be used.
For more information, see Database Performance and Query Optimization in the
Information Center .