Optimizing Sorts

Abstract

I speculate that in the 1980s and 1990s the biggest source of SQL performance problems was probably poor join order. If you made the wrong choice of table to start your joins you would likely end up in a world of pain. These days we have a reasonably mature CBO and a sophisticated runtime engine that between them support right-deep join trees, star transformations, and a wealth of other fancy features. On the one hand, these improvements mean that join order is much less of a problem for those of us working with an Oracle database than it was. On the other hand, I seem to spend a lot of time these days sorting out sorts. All too often a SQL statement will apparently hang and gobble up all the temporary table space, bringing the entire database to a screeching halt. This chapter is dedicated entirely to the important topic of optimizing sorts and for the most part focuses on writing or rewriting SQL efficiently.