Sorting database tables before compressing them improves the compression rate. Can we do better than the lexicographical order? For minimizing the number of runs in a run-length encoding compression scheme, the best approaches to row-ordering are derived from traveling salesman heuristics, although there is a significant trade-off between running time and compression. A new heuristic, Multiple Lists, which is a variant on Nearest Neighbor that trades off compression for a major running-time speedup, is a good option for very large tables. However, for some compression schemes, it is more important to generate long runs rather than few runs. For this case, another novel heuristic, Vortex, is promising. We find that we can improve run-length encoding up to a factor of 3 whereas we can improve prefix coding by up to 80 : these gains are on top of the gains due to lexicographically sorting the table. We prove that the new row reordering is optimal (within 10 ) at minimizing the runs of identical values within columns, in a few cases.
The compression of bitmap indexes also greatly benefits from table sorting. In some experiments, the sizes of the bitmap indexes are reduced by nearly an order of magnitude @cite_55 . Of course, everything else being equal, smaller indexes tend to be faster. Meanwhile, alternatives to the lexicographical order such as , reflected Gray-code or Hilbert orders are unhelpful on bitmap indexes @cite_55 . (We review an improved version of in .)
@cite_55
1966678916
Bitmap indexes must be compressed to reduce input output costs and minimize CPU usage. To accelerate logical operations (AND, OR, XOR) over bitmaps, we use techniques based on run-length encoding (RLE), such as Word-Aligned Hybrid (WAH) compression. These techniques are sensitive to the order of the rows: a simple lexicographical sort can divide the index size by 9 and make indexes several times faster. We investigate row-reordering heuristics. Simply permuting the columns of the table can increase the sorting efficiency by 40 . Secondary contributions include efficient algorithms to construct and aggregate bitmaps. The effect of word length is also reviewed by constructing 16-bit, 32-bit and 64-bit indexes. Using 64-bit CPUs, we find that 64-bit indexes are slightly faster than 32-bit indexes despite being nearly twice as large.