New algorithms for join and grouping operations

Computer Science - Research and Development

Volumn 27, Issue 1, 2012, Pages 3-27

  Graefe, Goetz ^a  

^a HEWLETT PACKARD LABORATORIES   (United States)

Author keywords

"Group by"; "Interesting orderings"; Buffer pool; Difference; Duplicate removal; Equivalence classes; Error; Grouping; Inner join; Intersection; Map reduce; Merging; Mistake; Order preserving; Outer join; Query execution algorithms; Query optimization choice; Query processing; Relational databases; Robust performance; Run generation; Semi join; Sort order; SQL; Union; Uniqueness

Indexed keywords

EID: 84857055947     PISSN: 18652034     EISSN: 18652042     Source Type: Journal    
DOI: 10.1007/s00450-011-0186-9     Document Type: Conference Paper

References (69)

1. G Antoshenkov M Ziauddin 1996 Query processing and optimization in Oracle Rdb VLDB J 5 4 229 237 10.1007/s007780050026 (Pubitemid 126704140)
2. Avnur R, Hellerstein JM (2000) Eddies: continuously adaptive query processing. In: SIGMOD, pp 261-272
3. C Ballinger R Fryer 1997 Born to be parallel: why parallel origins give Teradata an enduring performance edge IEEE Data Eng Bull 20 2 3 12
4. R Bayer K Unterauer 1977 Prefix B-Trees ACM Trans Database Syst 2 1 11 26 10.1145/320521.320530
5. D Bitton DJ DeWitt 1983 Duplicate record elimination in large data files ACM Trans Database Syst 8 2 255 265 0509.68109 10.1145/319983.319987 (Pubitemid 13589021)
6. Bizarro P, Babu S, DeWitt DJ, Widom J (2005) Content-based routing: different plans for different data. In: VLDB, pp 757-768 (Pubitemid 43991050)
7. Bratbergsengen K (1984) Hashing methods and relational algebra operations. In: VLDB, pp 323-333 (Pubitemid 16526839)
8. Carey MJ, Kossmann D (1997) On saying "enough already!" in SQL. In: SIGMOD, pp 219-230
9. MJ Carey D Kossmann 1997 Processing top n and bottom n queries IEEE Data Eng Bull 20 3 12 19
10. Chandrasekaran S, Franklin MJ (2002) Streaming queries over streaming data. In: VLDB, pp 203-214
11. Chaudhuri S, Shim K (1994) Including group-by in query optimization. In: VLDB, pp 354-366
12. Cole RL, Graefe G (1994) Optimization of dynamic query evaluation plans. In: SIGMOD, pp 150-160
13. Dean J, Ghemawat S (2004) MapReduce-simplified data processing on large clusters. In: OSDI, pp 137-150
14. DeWitt DJ, Gerber RH (1985) Multiprocessor hash-based join algorithms. In: VLDB, pp 151-164
15. DeWitt DJ, Katz RH, Olken F, Shapiro LD, Stonebraker M, Wood DA (1984) Implementation techniques for main memory database systems. In: SIGMOD, pp 1-8 (Pubitemid 15469530)
16. DeWitt DJ, Gerber RH, Graefe G, Heytens ML, Kumar KB, Muralikrishna M (1986) GAMMA-a high performance dataflow database machine. In: VLDB, pp 228-237
17. DeWitt DJ, Naughton JF, Burger J (1993) Nested loops revisited. In: PDIS, pp 230-242
18. Dittrich J-P, Seeger B, Taylor DS, Widmayer P (2002) Progressive merge join: a generic and non-blocking sort-based join algorithm. In: VLDB, pp 299-310
19. JC Freytag N Goodman 1989 On the translation of relational queries into iterative programs ACM Trans Database Syst 14 1 1 27 10.1145/62032.62033
20. Fushimi S, Kitsuregawa M, Tanaka H (1986) An overview of the system software of a parallel relational database machine GRACE. In: VLDB, pp 209-219
21. P Gassner GM Lohman KB Schiefer Y Wang 1993 Query optimization in the IBM DB2 family IEEE Data Eng Bull 16 4 4 18
22. G Graefe 1993 Query evaluation techniques for large databases ACM Comput Surv 25 2 73 170 10.1145/152610.152611 (Pubitemid 23712749)
23. G Graefe 2000 Dynamic query evaluation plans: some course corrections? IEEE Data Eng Bull 23 2 3 6
24. Graefe G (2003) Sorting and indexing with partitioned B-trees. In: CIDR
25. Graefe G (2003) Executing nested queries. In: BTW, pp 58-77
26. Graefe G (2006) Implementing sorting in database systems. ACM Comput. Surv. 38(3)
27. Graefe G (2006) B-tree indexes, interpolation search, and skew. In: DaMoN, p 5 (Pubitemid 46645163)
28. G Graefe 2007 Master-detail clustering using merged indexes Inform Forsch Entwickl 21 3-4 127 145 10.1007/s00450-007-0022-4
29. Graefe G (2010) A survey of B-tree locking techniques. ACM Trans Database Syst
30. Graefe G (2010) Robust sorting (in preparation)
31. G Graefe RL Cole 1995 Fast algorithms for universal quantification in large databases ACM Trans Database Syst 20 2 187 236 10.1145/210197.210202
32. Graefe G, DeWitt DJ (1987) The Exodus optimizer generator. In: SIGMOD, pp 160-172
33. Graefe G, Kuno HA (2010) Self-selecting, self-tuning, incrementally optimized indexes. In: EDBT, pp 371-381
34. Graefe G, Ward K (1989) Dynamic query evaluation plans. In: SIGMOD, pp 358-366
35. G Graefe A Linville LD Shapiro 1994 Sort versus hash revisited IEEE Trans Knowl Data Eng 6 6 934 944 10.1109/69.334883
36. Graefe G, Bunker R, Cooper S (1998) Hash joins and hash teams in Microsoft SQL Server. In: VLDB, pp 86-97
37. Graefe G, Kuno HA, Wiener JL (2009) Visualizing the robustness of query execution. In: CIDR
38. LM Haas MJ Carey M Livny A Shukla 1997 Seeking the truth about ad-hoc join costs VLDB J 6 3 241 256 10.1007/s007780050043 (Pubitemid 127373757)
39. Hagmann RB (1986) An observation on database buffering performance metrics. In: VLDB, pp 289-293
40. JM Hellerstein 1998 Optimization techniques for queries with expensive methods ACM Trans Database Syst 23 2 113 157 1604204 10.1145/292481.277627 (Pubitemid 128448477)
41. Helmer S, Westmann T, Moerkotte G (1998) Diag-join: an opportunistic join algorithm for 1:N relationships. In: VLDB, pp 98-109
42. CAR Hoare 1962 Quicksort Comput J 5 1 10 15 142216 0108.13601 10.1093/comjnl/5.1.10
43. Idreos S, Kersten ML, Manegold S (2007) Database cracking. In: CIDR, pp 68-78
44. Ioannidis YE, Kang YC (1990) Randomized algorithms for optimizing large join queries. In: SIGMOD, pp 312-321
45. Keller T, Graefe G, Maier D (1991) Efficient assembly of complex objects. In: SIGMOD, pp 148-157
46. Kemper A, Kossmann D, Wiesner C (1999) Generalised hash teams for join and group-by. In: VLDB, pp 30-41
47. Kim W (1980) A new way to compute the product and join of relations. In: SIGMOD, pp 179-187
48. Kitsuregawa M, Nakayama M, Takagi M (1989) The effect of bucket size tuning in the dynamic hybrid GRACE hash join method. In: VLDB, pp 257-266 (Pubitemid 20694274)
49. Knuth DE (1973) The art of computer programming, vol III: sorting and searching. Addison-Wesley, Reading
50. P-Å Larson 2003 External sorting: run formation revisited IEEE Trans Knowl Data Eng 15 4 961 972 10.1109/TKDE.2003.1209012
51. Larson P-Å, Graefe G (1998) Memory management during run generation in external sorting. In: SIGMOD, pp 472-483 (Pubitemid 128655992)
52. Li G (2010) On the design and evaluation of a new order-based join algorithm. MS-CS thesis, UC Irvine
53. Li Q, Shao M, Markl V, Beyer KS, Colby LS, Lohman GM (2007) Adaptively reordering joins during query execution. In: ICDE, pp 26-35 (Pubitemid 47422006)
54. Lohman GM (1988) Grammar-like functional rules for representing query optimization alternatives. In: SIGMOD, pp 18-27
55. V Markl GM Lohman V Raman 2003 LEO: An autonomic query optimizer for DB2 IBM Syst J 42 1 98 106 10.1147/sj.421.0098
56. Mohan C, Narang I (1992) Algorithms for creating indexes for very large tables without quiescing updates. In: SIGMOD, pp 361-370
57. Mohan C, Haderle DJ, Wang Y, Cheng JM (1990) Single table access using multiple indexes: optimization, execution, and concurrency control techniques. In: EDBT, pp 29-43
58. JEB Moss 1992 Working with persistent objects: to swizzle or not to swizzle IEEE Trans Softw Eng 18 8 657 673 10.1109/32.153378
59. Muralikrishna M, DeWitt DJ (1988) Equi-depth histograms for estimating selectivity factors for multi-dimensional queries. In: SIGMOD, pp 28-36
60. Nakayama M, Kitsuregawa M, Takagi M (1988) Hash-partitioned join method using dynamic destaging strategy. In: VLDB, pp 468-478
61. Pang H, Carey MJ, Livny M (1993) Memory-adaptive external sorting. In: VLDB, pp 618-629
62. B Salzberg 1989 Merging sorted runs using large main memory Acta Inform 27 3 195 215 1035834 0666.68059 10.1007/BF00572988 (Pubitemid 20682702)
63. Selinger PG, Astrahan MM, Chamberlin DD, Lorie RA, Price TG (1979) Access path selection in a relational database management system. In: SIGMOD, pp 23-34
64. LD Shapiro 1986 Join processing in database systems with large main memories ACM Trans Database Syst 11 3 239 264 10.1145/6314.6315
65. Shekita EJ, Carey MJ (1990) A performance evaluation of pointer-based joins. In: SIGMOD, pp 300-311
66. Simmen DE, Shekita EJ, Malkemus T (1996) Fundamental techniques for order optimization. In: SIGMOD, pp 57-67 (Pubitemid 126440756)
67. Youssefi K, Wong E (1979) Query processing in a relational database management system. In: VLDB, pp 409-417
68. Zeller H, Gray J (1990) An adaptive hash join algorithm for multiuser environments. In: VLDB, pp 186-197
69. Zhang W, Larson P-Å (1998) Buffering and read-ahead strategies for external mergesort. In: VLDB, pp 523-533