Article

Automated design of multidimensional clustering tables for relational databases

Authors:

Sam S. Lightstone,

Bishwaranjan BhattacharjeeAuthors Info & Claims

VLDB '04: Proceedings of the Thirtieth international conference on Very large data bases - Volume 30

Pages 1170 - 1181

Published: 31 August 2004 Publication History

Abstract

The ability to physically cluster a database table on multiple dimensions is a powerful technique that offers significant performance benefits in many OLAP, warehousing, and decision-support systems. An industrial implementation of this technique for the DB2® Universal Database^TM (DB2 UDB) product, called multidimensional clustering (MDC), which co-exists with other classical forms of data storage and indexing methods, was described in VLDB 2003. This paper describes the first published model for automating the selection of clustering keys in single-dimensional and multidimensional relational databases that use a cell/block storage structure for MDC. For any significant dimensionality (3 or more), the possible solution space is combinatorially complex. The automated MDC design model is based on what-if query cost modeling, data sampling, and a search algorithm for evaluating a large constellation of possible combinations. The model is effective at trading the benefits of potential combinations of clustering keys against data sparsity and performance. It also effectively selects the granularity at which dimensions should be used for clustering (such as week of year versus month of year). We show results from experiments indicating that the model provides design recommendations of comparable quality to those made by human experts. The model has been implemented in the IBM® DB2 UDB for Linux®, UNIX® and Windows® Version 8.2 release.

References

[1]

{1} "Transaction Processing Performance Council" http://www.tpc.org/default.asp.

[2]

{2} "DB2 Universal Database for Linux, UNIX and Windows" http://www-306.ibm.com/software/data/db2/udb/

[3]

{3} S. Agrawal, S. Chaudhuri, V.R. Narasayya, "Automated selection of materialized views and indexes in SQL databases". Proc. VLDB 2000, Cairo, Egypt.

Digital Library

[4]

{4} B. Bhattacharjee, S. Padmanabhan, T. Malkemus, T. Lai, L. Cranston, M. Huras, " Efficient Query Processing for Multi-Dimensionally Clustered Tables in DB2", Proc. VLDB 2003, Berlin, Germany.

Digital Library

[5]

{5} S. Chaudhuri, E. Christensen, G. Graefe, V. Narasayya, M. Zwilling, " Self-Tuning Technology in Microsoft SQL Server", IEEE Data Eng. Bul. 22(2), June 1999.

[6]

{6} S. Chaudhuri, V. Narasayya, "AutoAdmin 'What-if' Index Analysis Utility", Proc. SIGMOD, 1998, Seattle, USA.

Digital Library

[7]

{7} S. Chaudhuri, V. Narasayya, "Microsoft Index Tuning Wizard for SQL Server 7.0", Proc. SIGMOD, 1998, Seattle, USA.

Digital Library

[8]

{8} M. R. Frank, E. R. Omiecinski, S.B. Navathe, "Adaptive and Automated Index Selection in RDBMS", Proc. EDBT 1992, Vienna, Austria.

Digital Library

[9]

{9} P. J. Haas, J. F. Naughton, S. Seshadri, L. Stokes, "Sampling Based Estimation of the Number of Distinct Values of an Attribute", Proc. VLDB 1995, Zurich, Switzerland.

Digital Library

[10]

{10} P. J. Haas, L. Stokes, "Estimating the number of classes in a finite population", JASA, V. 93, Dec. 1998.

[11]

{11} S. Lightstone. D. Zilio, C. Zuzarte, G. Lohman, J. Rao, K. Cheung, "DB2 Design Advisor: More than just index selection", IDUG 2004, Orlando, USA.

[12]

{12} J. H Liou, S. B. Yao, "Multi-dimensional clustering for database organizations". Information Systems, 2:187-198, 1977.

[13]

{13} G. Lohman, G. Valentin, D. Zilio, M. Zuliani, A. Skelly, "DB2 Advisor: An optimizer smart enough to recommend its own indexes", Proc. ICDE 2000, San Diego, USA.

Digital Library

[14]

{14} V. Markl, F. Ramsak, R. Bayer, "Improving OLAP Performance by Multi-dimensional Hierarchical Clustering", Proc. IDEAS'99, Montreal, Canada.

Digital Library

[15]

{15} S. Padmanabhan, B. Bhattacharjee, T. Malkemus, L. Cranston, M. Huras, "Multi-Dimensional Clustering: A New Data Layout Scheme in DB2." SIGMOD 2003, San Diego, USA.

Digital Library

[16]

{16} N. Pendse, R. Creeth, "The OLAP Report", http://www.olapreport.com/.

[17]

{17} J. Rao, S. Lightstone, G. Lohman, D. Zilio, A. Storm, C. Garcia-Arellano, S. Fadden "DB2 Design Advisor: integrated automated physical database design", Proc. VLDB 2004, Toronto, Canada.

Digital Library

[18]

{18} J. Rao, C. Zhang, N. Megiddo, G. Lohman, "Automating physical database design in a parallel database.", Proc. SIGMOD 2002, Madison, USA.

Digital Library

[19]

{19} B. Schiefer, G. Valentin, "DB2 Universal Database Performance Tuning", IEEE Data Eng. Bul 22(2), June 1999.

[20]

{20} T. Stöhr, H. Märtens, E. Rahm, "Multi-Dimensional Database Allocation for Parallel Data Warehouses", Proc. VLDB 2000, Cairo, Egypt.

Digital Library

[21]

{21} T. Stohr, E. Rahm, "WARLOCK : A Data Allocation Tool for Parallel Warehouses", Proc. VLDB 2001, Rome, Italy (Software Demonstration).

Digital Library

Cited By

Zhang JLiu YZhou KLi GXiao ZCheng BXing JWang YCheng TLiu LRan MLi ZBoncz PManegold SAilamaki ADeshpande AKraska T(2019)An End-to-End Automatic Cloud Database Tuning System Using Deep Reinforcement LearningProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3300085(415-432)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3300085
Basu DLin QChen WVo HYuan ZSenellart PBressan S(2016)Regularized Cost-Model Oblivious Database Tuning with Reinforcement LearningTransactions on Large-Scale Data- and Knowledge-Centered Systems XXVIII - Volume 994010.1007/978-3-662-53455-7_5(96-132)Online publication date: 1-Jun-2016
https://dl.acm.org/doi/10.1007/978-3-662-53455-7_5
Suh YGhazal ACrolotte AKostamaa PChen XLebanon GWang HZaki M(2012)A new tool for multi-level partitioning in teradataProceedings of the 21st ACM international conference on Information and knowledge management10.1145/2396761.2398604(2214-2218)Online publication date: 29-Oct-2012
https://dl.acm.org/doi/10.1145/2396761.2398604
Show More Cited By

Index Terms

Automated design of multidimensional clustering tables for relational databases
1. Computing methodologies
  1. Machine learning
    1. Learning paradigms
      1. Unsupervised learning
        Cluster analysis
2. Information systems

Recommendations

Federating Object-Oriented and Relational Databases: The IRO-DB Experience
COOPIS '97: Proceedings of the Second IFCIS International Conference on Cooperative Information Systems

From the beginning of 1994 to the end of 1996, the IRO-DB (Interoperable Relational and Object-Oriented Databases) ESPRIT project has developed tools for accessing relational and object-oriented databases in an integrated way, and for designing and ...
Storing OWL ontologies in SQL3 object-relational databases
AIC'08: Proceedings of the 8th conference on Applied informatics and communications

When a large amount of data is stored in OWL files, it is not efficient to maintain and query those data. The OWL syntax is based on XML, which is a meta-markup language. Thus, it is suitable for data description and data exchange, rather than for data ...
Non-Relational Databases in Big Data
ICTCS '16: Proceedings of the Second International Conference on Information and Communication Technology for Competitive Strategies

These days' Big data is becoming a very essential component for the industries where large volume of data at very high speed is used to solve particular data problems. Generally, big data is first analyzed and then used with other available data in the ...

Comments

Information & Contributors

Information

Published In

cover image DL Hosted proceedings

VLDB '04: Proceedings of the Thirtieth international conference on Very large data bases - Volume 30

August 2004

1380 pages

ISBN:0120884690

Sponsors

VLDB Endowment: Very Large Database Endowment

Publisher

VLDB Endowment

Publication History

Published: 31 August 2004

Qualifiers

Article

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

5
Total Citations
View Citations
165
Total Downloads

Downloads (Last 12 months)3
Downloads (Last 6 weeks)0

Reflects downloads up to 03 Oct 2024

Other Metrics

View Author Metrics

Citations

Cited By

Zhang JLiu YZhou KLi GXiao ZCheng BXing JWang YCheng TLiu LRan MLi ZBoncz PManegold SAilamaki ADeshpande AKraska T(2019)An End-to-End Automatic Cloud Database Tuning System Using Deep Reinforcement LearningProceedings of the 2019 International Conference on Management of Data10.1145/3299869.3300085(415-432)Online publication date: 25-Jun-2019
https://dl.acm.org/doi/10.1145/3299869.3300085
Basu DLin QChen WVo HYuan ZSenellart PBressan S(2016)Regularized Cost-Model Oblivious Database Tuning with Reinforcement LearningTransactions on Large-Scale Data- and Knowledge-Centered Systems XXVIII - Volume 994010.1007/978-3-662-53455-7_5(96-132)Online publication date: 1-Jun-2016
https://dl.acm.org/doi/10.1007/978-3-662-53455-7_5
Suh YGhazal ACrolotte AKostamaa PChen XLebanon GWang HZaki M(2012)A new tool for multi-level partitioning in teradataProceedings of the 21st ACM international conference on Information and knowledge management10.1145/2396761.2398604(2214-2218)Online publication date: 29-Oct-2012
https://dl.acm.org/doi/10.1145/2396761.2398604
Bhattacharjee BMalkemus TLau SMcKeough SKirton JVon Boeschoten RKennedy JKlas WNeuhold E(2007)Efficient bulk deletes for multi dimensional clustered tables in DB2Proceedings of the 33rd international conference on Very large data bases10.5555/1325851.1325988(1197-1206)Online publication date: 23-Sep-2007
https://dl.acm.org/doi/10.5555/1325851.1325988
Zilio DRao JLightstone SLohman GStorm AGarcia-Arellano CFadden S(2004)DB2 design advisorProceedings of the Thirtieth international conference on Very large data bases - Volume 3010.5555/1316689.1316783(1087-1097)Online publication date: 31-Aug-2004
https://dl.acm.org/doi/10.5555/1316689.1316783

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents