Performance of SQL ORDER BY clause with VarChar Strings

Question

I'm looking to do a SELECT with an ORDER BY on a VARCHAR(200) field. Reading the DB2 v9.1 for zOS site on this it says

Ordering is performed in accordance with the comparison rules described in Language elements.

And in the rules for Character string comparisons says

Two strings are compared by comparing the corresponding bytes of each string. If the strings do not have the same length, the comparison is made with a temporary copy of the shorter string that has been padded on the right with blanks so that it has the same length as the other string.

My question is whether this means my ORDER BY clause will be slow as the amount of data increases because in the background a LENGTH() function call and then a padding is being carried out on every string in order to return the results in order?

Answer 1

You should use an index on the VARCHAR field. The index is a structure to optimize this process exactly.

Those comparisons you are referring to do take place when no index is present. However, if there is an index, there's constantly a structure maintaining the order of the table by that field, so ORDER BY does not incur significant additional overhead.

Answer 2

Order By typically incurs a sort operation as part of the query. If the number of rows being sorted is small enough to fit in a designated buffer of memory, the overhead is minimal. For larger result sets, in which there are too many rows to perform the sort in memory, the sort will require additional disk I/O to dump the data out to temporary storage and read it back in. A clustering index on your preferred sort key can greatly reduce the frequency and expense of sorts, but you can only choose one clustering index per table.