Piped versus non-piped sorts
If the final sorted list of data can be read in a single sequential pass, the results can be piped. Piping is quicker than non-piped ways of communicating the results of the sort. The optimizer chooses to pipe the results of a sort whenever possible.
Whether or not a sort is piped, the sort time depends on a number of factors, including the number of rows to be sorted, the key size and the row width. If the rows to be sorted occupy more than the space available in the sort heap, several sort passes are performed, in which each pass sorts a subset of the entire set of rows. Each sort pass is stored in a temporary table in the buffer pool. If there is not enough space in the buffer pool, pages from this temporary table might be written to disk. When all the sort passes are complete, these sorted subsets must be merged into a single sorted set of rows. If the sort is piped, the rows are handed directly to Relational Data Services as they are merged.
Sorting is required when:
No index exists to satisfy a requested ordering (for example a SELECT statement that uses the ORDER BY clause).
An index exists but sorting would be more efficient than using the index
An index is created.
An index is dropped, which causes index page numbers to be sorted.
Sorting involves two steps:
1.A sort phase
A sort can be overflowed or non-overflowed.
If the sorted data cannot fit entirely into the sort heap, which is a block of memory that is allocated each time a sort is performed, it overflows into a temporary table owned by the database. Sorts that do not overflow always perform better than those that do.
2.Return of the results of the sort phase.
The return can be piped or non-piped. If sorted information can return directly without requiring a temporary table to store a final, sorted list of data, it is a piped sort. If the sorted information requires a temporary table to be returned, it is a non-piped sort. A piped sort always performs better than a non-piped sort.
沒有留言:
張貼留言