Discovering and exploiting statistical properties for query optimization in relational databases: A survey

Statistical Analysis and Data Mining

Volumn 1, Issue 4, 2009, Pages 223-250

  Haas, Peter J ^a   Ilyas, Ihab F ^b   Lohman, Guy M ^a   Markl, Volker ^c  

^a IBM ALMADEN RESEARCH CENTER   (United States)

^b UNIVERSITY OF WATERLOO   (Canada)

^c TECHNISCHE UNIVERSITÄT BERLIN   (Germany)

Author keywords

Data mining; Query feedback; Query optimization; relational database; Sampling; Statistical structure

Indexed keywords

ALGEBRA; DATA MINING; QUERY PROCESSING; STATISTICS; SURVEYS;

DATABASE DESIGN; DATABASES INTEGRATIONS; MANAGEMENT IS; QUERIES OPTIMIZATION; QUERY FEEDBACK; RELATIONAL DATABASE; RELATIONAL DATABASE MANAGEMENT SYSTEMS; STATISTICAL FEATURES; STATISTICAL PROPERTIES; STATISTICAL STRUCTURES;

SAMPLING;

EID: 62249189663     PISSN: 19321872     EISSN: 19321864     Source Type: Journal    
DOI: 10.1002/sam.10016     Document Type: Review

References (89)

1. M. M. Astrahan, M. W. Blasgen, D. D. Chamberlin, J. Gray, W. F. King III, B. G. Lindsay, R. A. Lorie, J. W. Mehl, T. G. Price, G. R. Putzolu, M. Schkolnick, P. G. Selinger, D. R. Slutz, H. R. Strong, P. Tiberio, I. L. Traiger, B. W. Wade, and R. A. Yost, System R: A relational data base management system, IEEE Comput 12(5) (1979), 42-48.
2. E. F. Codd, A relational model of data for large shared data banks, Commun ACM 13(6) (1970), 377-387.
3. G. Antoshenkov and M. Ziauddin, Query processing and optimization in Oracle Rdb, VLDB J 5(4) (1996), 229-237.
4. P. Gassner, G. M. Lohman, K. B. Schiefer, and Y. Wang, Query optimization in the IBM DB2 family, IEEE Data Eng. Bull 16(4) (1993), 4-18.
5. G. Graefe, The Cascades framework for query optimization, IEEE Data Eng Bull 18(3) (1995), 19-29.
6. M. Jarke and J. Koch, Query optimization in database systems, ACM Comput Surv 16(2) (1984), 111-152.
7. P. G. Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price, Access path selection in a relational database management system, In Proceedings SIGMOD, Boston, MA, 1979, 23-34.
8. Y. Matias, J. S. Vitter, and M. Wang, Wavelet-based histograms for selectivity estimation, In Proceedings SIGMOD, Seattle, WA, 1998, 448-459.
9. R. Berinde, A. C. Gilbert, P. Indyk, H. Karloff, and M. J. Strauss, Combining geometry and combinatorics: a unified approach to sparse signal recovery, 2008, Preprint, Available at http://people.csail.mit.edu/indyk/ rip2expand.pdf.
10. A. C. Gilbert, S. Guha, P. Indyk, Y. Kotidis, S. Muthukrishnan, and M. Strauss, Fast, small-space algorithms for approximate histogram maintenance. In Proceedings STOC, Montreal, Quebec, Canada, 2002, 389-398.
11. J. Boulos, Y. Viemont, and K. Ono, A neural networks approach for query cost evaluation, Trans Inf Proc Soc Jpn 38(12) (1997), 2566-2575.
12. H. Lu and R. Setiono, Effective query size estimation using neural networks, Appl Intell 16(3) (2002), 173-183.
13. L. Getoor, B. Taskar, and D. Koller, Selectivity estimation using probabilistic models, In Proceedings SIGMOD, Santa Barbara, CA, 2001, 461-472.
14. A. Balmin, T. Eliaz, J. Hornibrook, L. Lim, G. M. Lohman, D. E. Simmen, M. Wang, and C. Zhang, Cost-based optimization in DB2 XML, IBM Syst J 45(2) (2006), 299-320.
15. J. Vitter, Random sampling with a reservoir, ACM Trans Math Softw 11(1) (1985), 37-57.
16. V. Poosala, Y. E. Ioannidis, P. J. Haas, and E. J. Shekita, Improved histograms for selectivity estimation of range predicates, In Proceedings SIGMOD, Montreal, Canada, 1996, 294-305.
17. M. Greenwald and S. Khanna, Space-efficient online computation of quantile summaries, In Proceedings SIGMOD, Santa Barbara, CA, 2001, 58-66.
18. Q. Zhang and W. Wang, An efficient algorithm for approximate biased quantile computation in data streams, In Proceedings CIKM, Lisbon, Portugal, 2007, 1023-1026.
19. Y. E. Ioannidis, The history of histograms (abridged), In Proceedings VLDB, Berlin, Germany, 2003, 19-30.
20. M. Muralikrishna and D. J. DeWitt, Equi-depth histograms for estimating selectivity factors for multi-dimensional queries, In Proceedings SIGMOD, Chicago, IL, 1988, 28-36.
21. T. Eavis and A. Lopez, rK-Hist: An R-tree based histogram for multi-dimensional selectivity estimation, In Proceedings CIKM, Lisbon, Portugal, 2007, 475-484.
22. N. Thaper, S. Guha, P. Indyk, and N. Koudas, Dynamic multidimensional histograms, In Proceedings SIGMOD, Madison, WI, 2002, 428-439.
23. H. Wang and K. C. Sevcik, A multi-dimensional histogram for selectivity estimation and fast approximate query answering, In Proceedings CASCON, Toronto, Ontario, Canada, 2003, 328-342.
24. K. Beyer, P. J. Haas, B. Reinwald, Y. Sismanis, and R. Gemulla, On synopses for distinct-value estimation under multiset operations, In Proceedings SIGMOD, Beijing, China, 2007, 199-210.
25. Y. Huhtala, J. Kärkkäinen, P. Porkka, and H. Tiovonen, TANE: An efficient algorithm for discovering functional and approximate dependencies, Comput J 42(2) (1999), 100-111.
26. R. Agrawal and R. Srikant, Fast algorithms for mining association rules in large databases, In Proceedings VLDB, Santiago de Chile, Chile, 1994, 487-499.
27. S. Bell and P. Brockhausen, Discovery of constraints and data dependencies in databases, Proceedings European Conference Machine Learning (ECML-95), Lecture Notes in Artificial Intelligence 914, Springer, Berlin, 1995, 267-270.
28. J.-M. Petit, F. Toumani, J.-F. Boulicaut, and J. Kouloumdjian, Towards the reverse engineering of denormalized relational databases, In Proceedings ICDE, New Orleans, LA, 1996, 218-227.
29. P. Godfrey, J. Gryz, and C. Zuzarte, Exploiting constraintlike data characterizations in query optimization, In Proceedings SIGMOD, Santa Barbara, CA, 2001, 582-592.
30. J. Gryz, K. B. Schiefer, J. Zheng, and C. Zuzarte, Discovery and application of check constraints in DB2, In Proceedings ICDE, Heidelberg, Germany, 2001, 551-556.
31. J. Edmonds, J. Gryz, D. Liang, and R. J. Miller, Mining for empty rectangles in large data sets, In Proceedings ICDT, London, UK, 2001, 174-188.
32. J. Gryz and D. Liang, Holes in joins, J Intell Inf Syst, 26(3) (2006), 247-268.
33. M. Siegel, E. Sciore, and S. Salveter, A method for automatic rule derivation to support semantic query optimization, ACM Trans Database Syst, 17(4) (1992), 563-600.
34. C. T. Yu and W. Sun, Automatic knowledge acquisition and maintenance for semantic query optimization, IEEE Trans Knowl Data Eng 1(3) (1989), 362-375.
35. R. Srikant and R. Agrawal, Mining quantitative association rules in large relational tables, In Proceedings SIGMOD, Montreal, Canada, 1996, 1-12.
36. P. Brown, P. J. Haas, J. Myllymaki, H. Pirahesh, B. Reinwald, and Y. Sismanis, Toward automated large-scale information integration and discovery, Data Management in a Connected World, Springer, Berlin, 2005, 161-180.
37. Y. Sismanis, P. J. Haas, and B. Reinwald, GORDIAN: Efficient and scalable discovery of all composite keys, In Proceedings VLDB, Seoul, Korea, 2006, 691-702.
38. J. Gray, S. Chaudhuri, A. Bosworth, A. Layman, D. Reichart, M. Venkatrao, F. Pellow, and H. Pirahesh, Data cube: A relational aggregation operator generalizing group-by, crosstab, and sub-totals, J Data Min Knowl Discov 1 (1997), 29-53.
39. D. Gunopulos, R. Khardon, H. Mannila, S. Saluja, H. Toivonen, and R. S. Sharma, Discovering all most specific sentences, ACM Trans Database Syst 28(2) (2003), 140-174.
40. A. Deshpande, M. Garofalakis, and R. Rastogi, Independence is good: dependency-based histogram synopses for high-dimensional data, In Proceedings SIGMOD, Santa Barbara, CA, 2001, 199-210.
41. L. Getoor and B. Taskar (eds), Introduction to Statistical Relational Learning, MIT Press, Cambridge, Massachusetts, 2007.
42. S. Babu, P. Bizarro, and D. DeWitt, Proactive reoptimization, In Proceedings SIGMOD, Baltimore, Maryland, USA, 2005, 107-118.
43. C. Chen and N. Roussopoulos, Adaptive selectivity estimation using query feedback, In Proceedings SIGMOD, Minneapolis, Minnesota, 1994, 161-172.
44. B. S. Lee, L. Chen, J. Buzas, and V. Kannoth, Regressionbased self-tuning modeling of smooth user-defined function costs for an object-relational database management system query optimizer, Comput J 47(6) (2004) 673-693.
45. Q. Zhu and P. Larson, Building regression cost models for multidatabase systems, In Proceedings IEEE PDIS, Miami Beach, FL, 1996.
46. Z. He, B. S. Lee, and R. R. Snapp, Self-tuning UDF cost modeling using the memory-limited quadtree, In Proceedings EDBT, Heraklion, Crete, Greece, 2004.
47. N. Zhang, P. J. Haas, V. Josifovski, G. M. Lohman, and C. Zhang, Statistical learning techniques for costing XML queries, In Proceedings VLDB, Trondheim, Norway, 2005, 289-300.
48. R. Gemulla, W. Lehner, and P. J. Haas, Maintaining Bernoulli samples over evolving multisets, In Proceedings PODS, Beijing, China, 2007, 93-102.
49. R. Gemulla, W. Lehner, and P. J. Haas, Maintaining bounded-size sample synopses of evolving datasets, VLDB J 17(2) (2008), 173-202.
50. P. J. Haas and C. König, A bi-level Bernoulli scheme for database sampling, In Proceedings SIGMOD, Paris, France, 2004, 275-286.
51. P. G. Brown and P. J. Haas, Techniques for warehousing of sample data, In Proceedings ICDE, Atlanta, GA, 2006.
52. M. Charikar, S. Chaudhuri, R. Motwani, and V. R. Narasayya, Towards estimation error guarantees for distinct values, In Proceedings PODS, Dallas, Texas, 2000, 268-279.
53. P. J. Haas, Y. Liu, and L. Stokes, An estimator of the number of species from quadrat sampling, Biometrics 62(1) (2006), 135-141.
54. P. J. Haas and L. Stokes, Estimating the number of classes in a finite population, J Am Stat Assoc 93(444) (1998), 1475-1487.
55. E. Cohen, N. Grossaug, and H. Kaplan, Processing top-k queries from samples, In Proceedings 2nd Conference Future Networking Tech. (CoNext), Lisbon, Portugal, 2006, 7.
56. M. Wu and C. Jermaine, A Bayesian method for guessing the extreme values in a data set, In Proceedings VLDB, Vienna, Austria, 2007, 471-482.
57. C.-E. Särndal, B. Swensson, and J.Wretman, Model Assisted Survey Sampling, Springer, New York, 1992.
58. P. G. Brown and P. J. Haas, BHUNT: Automatic discovery of fuzzy algebraic constraints in relational data, In Proceedings VLDB, Berlin, Germany, 2003, 668-679.
59. H. Scheffé and J. W. Tukey, Nonparametric estimation. I. Validation of order statistics, Ann Math Stat, 16(2) (1945), 187-192.
60. J. W. Tukey, Nonparametric estimation II. Statistically equivalent blocks and tolerance regions-The continuous case, Ann Math Statist 18 (1947), 529-539.
61. I. F. Ilyas, V. Markl, P. J. Haas, P. G. Brown, and A. Aboulnaga, CORDS: Automatic discovery of correlations and soft functional dependencies, In Proceedings SIGMOD, Paris, France, 2004, 647-658.
62. H. Cramér, Mathematical Methods of Statistics, Princeton University Press, Princeton, New Jersey, 1948.
63. T. R. C. Read and N. A. C. Cressie, Goodness-of-Fit Statistics for Discrete Multivariate Data, Springer, New York, 1988.
64. P. Bruni, T. Berman, J. Iczkovits, B. Soetarman, B. Steegmans, and M. Turner, DB2 9 for z/OS: New Tools for Query Optimization, IBM Redbook Series SG24-7421-00, IBM Corporation, Armonk, New York, 2007.
65. S. Chaudhuri and V. Narasayya, Automating statistics management for query optimizers, IEEE Trans Knowl Data Eng 13(1) (2001), 7-20.
66. A. El-Helw, I. F. Ilyas, W. Lau, V. Markl, and C. Zuzarte, Collecting and maintaining just-in-time statistics, In Proceedings ICDE, Istanbul, Turkey, 2007, 516-525.
67. N. Bruno and S. Chaudhuri, Exploiting statistics on query expressions for optimization, In Proceedings SIGMOD, Madison, WI, 2002, 263-274.
68. K. K. Chen, Influence query optimization with optimization profiles and statistical views in DB2 9: Optimal query performance in DB2 9 for Linux, UNIX, and Windows, 2006, Available at www.ibm.com/developerworks/db2/library/ techarticle/dm0612chen.
69. M. Stillger, G. M. Lohman, V. Markl, and M. Kandil, LEO - DB2's LEarning Optimizer, In Proceedings VLDB, Rome, Italy, 2001, 19-28.
70. A. Aboulnaga, P. J. Haas, M. Kandil, S. Lightstone, G. Lohman, V. Markl, I. Popivanov, and V. Raman, Automated statistics collection in DB2 UDB, In Proceedings VLDB, Toronto, Ontario, Canada, 2004.
71. L. Lim, M. Wang, and J. S. Vitter, SASH: A self-adaptive histogram set for dynamically changing workloads, In Proceedings VLDB, Berlin, Germany, 2003, 369-380.
72. V. Raman and G. Swart, How to wring a table dry: Entropy compression of relations and querying of compressed relations, In Proceedings VLDB, Seoul, Korea, 2006, 858-869.
73. P. J. Haas, F. Hueske, and V. Markl, Detecting attribute dependencies from query feedback, In Proceedings VLDB, Vienna, Austria, 2007, 830-841.
74. P. B. Gibbons, Y. Matias, and V. Poosala, Fast incremental maintenance of approximate histograms, In Proceedings VLDB, Athens, Greece, 1997.
75. V. Poosala and Y. E. Ioannidis, Selectivity estimation without the attribute value independence assumption, In Proceedings VLDB, Athens, Greece, 1997, 486-495.
76. A. Aboulnaga and S. Chaudhuri, Self-tuning histograms: Building histograms without looking at data, In Proceedings SIGMOD, Philadelphia, PA, 1999, 181-192.
77. N. Bruno, S. Chaudhuri, and L. Gravano, STHoles: a multidimensional workload-aware histogram, In Proceedings SIGMOD, Santa Barbara, CA, 2001, 211-222.
78. U. Srivastava, P. J. Haas, V. Markl, and N. Megiddo, ISOMER: Consistent histogram construction using query feedback, In Proceedings ICDE, Atlanta, GA, 2006.
79. E. Amaldi and V. Kann, The complexity and approximability of finding maximum feasible subsystems of linear relations, Theor Comput Sci 147(1-2) (1995), 181-210.
80. A. Behm, V. Markl, P. J. Haas, and K. Murthy, Integrating query-feedback based statistics into Informix Dynamic Server, In Proceedings BTW, Aachen, Germany, 2007, 582-601.
81. A. Behm, DB2 learning optimizer, query feedback, frequent values and quantiles, Thesis Assistentenarbeit, Department of Information Technology, Berufsakademie Stuttgart, 2005.
82. V. Markl, V. Raman, D. Simmen, G. Lohman, H. Pirahesh, and M. Cilimdzic, Robust query processing through progressive optimization, In Proceedings SIGMOD, Paris, France, 2004, 659-670.
83. V. Markl, P. J. Haas, M. Kutsch, N. Megiddo, and T. M. Tran, Consistent selectivity estimation via maximum entropy, VLDB J 16(1) (2007), 55-76.
84. S. S. Parekh, K. Rose, J. L. Hellerstein, S. Lightstone, M. Huras, and V. Chang, Managing the performance impact of administrative utilities, In Proceedings DSOM, Heidelberg, Germany, 2003, 130-142.
85. D. A. Cohn, Z. Ghahramani, and M. I. Jordan, Active learning with statistical models, J Artif Intell Res 4 (1997), 129-145.
86. B. Bhattacharjee, J. S. Glider, R. A. Golding, G. M. Lohman, V. Markl, H. Pirahesh, J. Rao, R. Rees, and G. Swart, Impliance: A next generation information management appliance, In Proceedings CIDR, Asilomar, CA, 2007, 351-362.
87. R. Motwani, J. Widom, A. Arasu, B. Babcock, S. Babu, M. Datar, G. S. Manku, C. Olston, J. Rosenstein, and R. Varma, Query processing, approximation, and resource management in a data stream management system, In Proceedings CIDR, Asilomar, CA, 2003.
88. N. N. Dalvi and D. Suciu, Management of probabilistic data: foundations and challenges, In Proceedings PODS, Beijing, China, 2007, 1-12.
89. R. Jampani, F. Xu, M. Wu, L. L. Perez, C. M. Jermaine, and P. J. Haas, MCDB: a Monte Carlo approach to managing uncertain data, In Proceedings SIGMOD, Vancouver, BC, Canada, 2008, 687-700.