sorting - How Expensive is SQL ORDER BY? -

i don't quite understand how sql command sort large resultset. done in memory on fly (i.e. when query perfomed)?

is going faster sort using order in sql rather sort linked list of objects containing results in language java (assuming fast built-in sort, using quicksort)?

it more efficient sort data in database. databases designed deal large data volumes. , there various optimizations available database not available middle tier. if plan on writing hyper-efficient sort routine in middle tier takes advantage of information have data database doesn't (i.e. farming data out cluster of dozens of middle tier machines sort never spills disk, taking advantage of fact data ordered choose algorithm wouldn't particularly efficient), can beat database's sort speed. tends rare.

depending on query, example, database optimizer may choose query plan returns data in order without performing sort. example, database knows data in index sorted may choose index scan return data in order without ever having materialize , sort entire result set. if have materialized entire result, needs columns sorting , sort of row identifier (i.e. rowid in oracle) rather sorting entire row of data naive middle tier implementation do. example, if have composite index on (col1, col2) , decide sort on upper(col2), lower(col1), database read col1 & col2 values index, sort row identifiers, , go fetch data table. of course, database doesn't have this-- optimizer take account cost of doing sort against cost of fetching data table or various indexes. database may conclude efficient approach table scan, read entire row memory, , sort it. may conclude leveraging index results in more i/o fetch data makes reducing or eliminating sort costs.