Databases Reference
In-Depth Information
5
Optimizing Sort
Operations
In this chapter, we will cover:
F
Sorting—in-memory and on-disk
F
Sorting and indexing
F
Writing top
n
queries and ranking
F
Using count, min/max, and group-by
F
Avoiding sorting in set operations: union, minus, and intersect
F
Troubleshooting temporary tablespaces
Introduction
In this chapter, we analyze some performance issues related to the most time-consuming
operation in the database—sort operations.
In the next few recipes, you will see that sorting is related not only to the order-by clause
in an SQL query, but also to other type of statements, such as group by and distinct, set
operations, ranking, certain kinds of joins and subqueries, as well as index creation.
In the first recipe, we will see the difference between in-memory and on-disk sort operations,
and the differences between optimal, one pass, and multi-pass sort operations.
The second recipe is about sorting and indexing. In this recipe, observe how an index can
change the execution plan of a query, hence improving the performance by reducing or
avoiding sort operations altogether.
