6

I couldn't find the answer by googling.

I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle).

In what circumstances can an order by clause effect negatively on performance?

Update

I found Bad performance of SQL query due to ORDER BY clause. Is there any other circumstances where an order by clause may negatively effect performance?

3 Answers 3

8

An ORDER BY clause will always add a sort operation if there is no usable index to retrieve the data in order. For larger sets of data the data being sorted may be written to disk adding additional processing. For smaller result sets where indexes are being used, there may be no requirement for a sort step.

If sorting is required, the database will have to retrieve all the sort key data and sort it before presenting results. This can delay the availability of the first rows. If you only want the first few rows of a large set, this can generate a lot of extra work for the database.

Than being said, I have rarely seen an ORDER BY clause a significant performance impact. I recall one case where a developer attempted to improve performance by retrieving an unsorted result set and sorting it in the client, but it did not work well.

I avoid the ORDER BY clause unless there is a requirement for data to appear in a given order. If there is a requirement to present data in a given order, I use the ORDER BY clause. If there is a question about performance, I test the query against a production database before testing alternatives.

2

It depends on the query and size of result set. If sorting can be done in memory its still "fast". But if its too large for memory Oracle will write the result to TEMP tablespace and this could be worse. You could put the TEMP TS on SDD/Flash Disk this will improve sort performance.

0
1

ORDER BY on large tables can be real performance disaster , try to apply the ORDER BY clause on smallest possible dataset , in theory the optimizer does good job to choose the best way to run the ORDER BY so performance is not affected significantly but in real heavy environments i saw ORDER BY clause which affect the performance significantly. For example a view which select from 11 tables and returns milions of records , if you add an ORDER BY to the select from this view then you wait 10 times more and the execution plan cost increase. Another problem might happened when the system has luck of memory then applying the ORDER BY might be heavier.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.