Implementing sorting in database systems

Graefe, Goetz

doi:10.1145/1132960.1132964

Cited by 107 publications

(53 citation statements)

References 40 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Moreover, a recent survey on sorting in database systems pointed out that replacement selection is one of the most used techniques for external sorting in databases [3].…”

Section: Related Workmentioning

confidence: 99%

“…This reduces the number of runs, allowing the merge process to reduce its fan in, and the chances to perform multiple I/O passes during the merge phase. Finally, although it is not the fastest in-memory sorting strategy, it offers a good trade off for its value: it generates smaller I/O by creating larger runs at the cost of possibly more in-core computational effort, compared to other faster in-core methods [3].…”

Section: Introductionmentioning

confidence: 99%

See 1 more Smart Citation

Two-way replacement selection

2010

View full text Add to dashboard Cite

The performance of external sorting using merge sort is highly dependent on the length of the runs generated. One of the most commonly used run generation strategies is Replacement Selection (RS) because, on average, it generates runs that are twice the size of the memory available. However, the length of the runs generated by RS is downsized for data with certain characteristics, like inputs sorted inversely with respect to the desired output order.The goal of this paper is to propose and analyze two-way replacement selection (2WRS), which is a generalization of RS obtained by implementing two heaps instead of the single heap implemented by RS. The appropriate management of these two heaps allows generating runs larger than the memory available in a stable way, i.e. independent from the characteristics of the datasets. Depending on the changing characteristics of the input dataset, 2WRS assigns a new data record to one or the other heap, and grows or shrinks each heap, accommodating to the growing or decreasing tendency of the dataset. On average, 2WRS creates runs of at least the length generated by RS, and longer for datasets that combine increasing and decreasing data subsets. We tested both algorithms on large datasets with different characteristics and 2WRS achieves speedups at least similar to RS, and over 2.5 when RS fails to generate large runs.

show abstract

“…Moreover, a recent survey on sorting in database systems pointed out that replacement selection is one of the most used techniques for external sorting in databases [3].…”

Section: Related Workmentioning

confidence: 99%

Section: Introductionmentioning

confidence: 99%

Two-way replacement selection

2010

View full text Add to dashboard Cite

show abstract

“…Much research has been done on adaptive sorting [18] exploiting near-sortedness. Graefe's survey [19] discussed how sorting is implemented in database systems with many tricks and optimizations.…”

Section: Impact On Optimizer Search Spacementioning

confidence: 99%

Optimized query evaluation using cooperative sorts

Cao

Bramandia

Chan

et al. 2010

2010 IEEE 26th International Conference on Data Engineering (ICDE 2010)

View full text Add to dashboard Cite

Abstract-Many applications require sorting a table over multiple sort orders: generation of multiple reports from a table, evaluation of a complex query that involves multiple instances of a relation, and batch processing of a set of queries. In this paper, we study how multiple sortings of a table can be efficiently performed. We introduce a new evaluation technique, called cooperative sort, that exploits the relationships among the input set of sort orders to minimize I/O operations for the collection of sort operations. To demonstrate the efficiency of the proposed scheme, we implemented it in PostgreSQL and evaluated its performance using both TPC-DS benchmark and synthetic data. Our experimental results show significant performance improvement over the traditional non-cooperative sorting scheme.

show abstract

“…The most well-known external-memory sorting algorithm is multi-way merge sort [1,8,15,22,28,29,40,42,44]. The multi-way merge sort is formalized in the disk-access machine 1 (DAM) model of Aggarwal and Vitter [1].…”

Section: Introductionmentioning

confidence: 99%

“…Because there are few scans to begin with, even if we only do one fewer scan, the cost of a merge sort is decreased by a significant percentage. Run generation has further advantages in databases even when a full sort is not required [22,24].Replacement Selection. The classic algorithm for run generation is called replacement selection [20,26,28].…”

mentioning

confidence: 99%

Run Generation Revisited: What Goes Up May or May Not Come Down

Bender

McCauley

McGregor

et al. 2015

Lecture Notes in Computer Science

View full text Add to dashboard Cite

In this paper, we revisit the classic problem of run generation. Run generation is the first phase of external-memory sorting, where the objective is to scan through the data, reorder elements using a small buffer of size M , and output runs (contiguously sorted chunks of elements) that are as long as possible.We develop algorithms for minimizing the total number of runs (or equivalently, maximizing the average run length) when the runs are allowed to be sorted or reverse sorted. We study the problem in the online setting, both with and without resource augmentation, and in the offline setting.• We analyze alternating-up-down replacement selection (runs alternate between sorted and reverse sorted), which was studied by Knuth as far back as 1963. We show that this simple policy is asymptotically optimal. Specifically, we show that alternating-up-down replacement selection is 2-competitive and no deterministic online algorithm can perform better.• We give online algorithms having smaller competitive ratios with resource augmentation. Specifically, we exhibit a deterministic algorithm that, when given a buffer of size 4M , is able to match or beat any optimal algorithm having a buffer of size M . Furthermore, we present a randomized online algorithm which is 7/4-competitive when given a buffer twice that of the optimal.• We demonstrate that performance can also be improved with a small amount of foresight. We give an algorithm, which is 3/2-competitive, with foreknowledge of the next 3M elements of the input stream. For the extreme case where all future elements are known, we design a PTAS for computing the optimal strategy a run generation algorithm must follow.• We present algorithms tailored for "nearly sorted" inputs which are guaranteed to have optimal solutions with sufficiently long runs. External-memory sorting algorithms are tailored for data sets too large to fit in main memory. Generally, these algorithms begin their sort by bringing chunks of data into main memory, sorting within memory, and writing back out to disk in sorted sequences, called runs [15,19,26,34].We revisit the classic problem of how to maximize the length of these runs, the run-generation problem. The run-generation problem has been studied in its various guises for over 50 years [14,[17][18][19]25,30,31,34].The most well-known external-memory sorting algorithm is multi-way merge sort [1,8,15,22,28,29,40,42,44]. The multi-way merge sort is formalized in the disk-access machine 1 (DAM) model of Aggarwal and Vitter [1]. If M is the size of RAM and data is transferred between main memory and disk in blocks of size B, then an M/B-way merge sort has a complexity of O (N/B) log M/B (N/B) I/Os, where N is the number of elements to be sorted. This is the best possible [1].A top-down description of multi-way merge sort follows. Divide the input into M/B subproblems, recursively sort each subproblem, and merge them together in one final scan through the input. The base case is reached when each subproblem has size O(M ), and therefore fit...

show abstract

Implementing sorting in database systems

Cited by 107 publications

References 40 publications

Two-way replacement selection

Two-way replacement selection

Optimized query evaluation using cooperative sorts

Run Generation Revisited: What Goes Up May or May Not Come Down

Contact Info

Product

Resources

About