Recommending materialized views and indexes with the IBM DB2 design advisor

Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the...

Ausführliche Beschreibung

Gespeichert in:
Bibliographische Detailangaben
Hauptverfasser: Zilio, D.C., Zuzarte, C., Lightstone, S., Wenbin Ma, Lohman, G.M., Cochrane, R.J., Pirahesh, H., Colby, L., Gryz, J., Alton, E., Valentin, G.
Format: Tagungsbericht
Sprache:eng
Schlagworte:
Online-Zugang:Volltext bestellen
Tags: Tag hinzufügen
Keine Tags, Fügen Sie den ersten Tag hinzu!
container_end_page 187
container_issue
container_start_page 180
container_title
container_volume
creator Zilio, D.C.
Zuzarte, C.
Lightstone, S.
Wenbin Ma
Lohman, G.M.
Cochrane, R.J.
Pirahesh, H.
Colby, L.
Gryz, J.
Alton, E.
Valentin, G.
description Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the decision intractable for humans and computationally challenging for computer algorithms. Even heuristic-based algorithms can be impractical in real systems. In this paper, we present an advanced tool that uses the query optimizer itself to both suggest and evaluate candidate MVs and indexes, and a simple, practical, and effective algorithm for rapidly finding good solutions even for large workloads. The algorithm trades off the cost for updates and storing each MV or index against its benefit to queries in the workload. The tool autonomically captures the workload, database, and system information, optionally permits sampling of candidate MVs to better estimate their size, and exploits multi-query optimization to construct candidate MVs that will benefit many queries, over which their maintenance cost can then be amortized cost-effectively. We describe the design of the system and present initial experiments that confirm the quality of its results on a database and workload drawn from a real customer database.
doi_str_mv 10.1109/ICAC.2004.1301362
format Conference Proceeding
fullrecord <record><control><sourceid>ieee_6IE</sourceid><recordid>TN_cdi_ieee_primary_1301362</recordid><sourceformat>XML</sourceformat><sourcesystem>PC</sourcesystem><ieee_id>1301362</ieee_id><sourcerecordid>1301362</sourcerecordid><originalsourceid>FETCH-LOGICAL-i88t-e24b25b996745585791bcceb592fc96fb943948dd6b9a716a7ebb410d540dd713</originalsourceid><addsrcrecordid>eNotj8tKAzEYRgMiKHUeoLjJC8yYe_Iv7XhpsSKU7ksy-aeNdKYyGVr16S3Yb3N2h_MRMuWs4pzBw6J-rCvBmKq4ZFwacUUKsI5ZA1pwrvQNKXL-ZOdJkE7YW_K2wubQddjH1G9p50cckt-nX4z0mPCUqe8jTX3Eb8z0lMYdHXdIF7N3-jQTNGJO2576eEz5MNyR69bvMxYXTsj65Xldz8vlx-s5bVkm58YShQpCBwBjldZOW-ChaTBoEG0Dpg2gJCgXowngLTfeYgiKs6gVi9FyOSH3_9qEiJuvIXV--NlcHss_gLtLLw</addsrcrecordid><sourcetype>Publisher</sourcetype><iscdi>true</iscdi><recordtype>conference_proceeding</recordtype></control><display><type>conference_proceeding</type><title>Recommending materialized views and indexes with the IBM DB2 design advisor</title><source>IEEE Electronic Library (IEL) Conference Proceedings</source><creator>Zilio, D.C. ; Zuzarte, C. ; Lightstone, S. ; Wenbin Ma ; Lohman, G.M. ; Cochrane, R.J. ; Pirahesh, H. ; Colby, L. ; Gryz, J. ; Alton, E. ; Valentin, G.</creator><creatorcontrib>Zilio, D.C. ; Zuzarte, C. ; Lightstone, S. ; Wenbin Ma ; Lohman, G.M. ; Cochrane, R.J. ; Pirahesh, H. ; Colby, L. ; Gryz, J. ; Alton, E. ; Valentin, G.</creatorcontrib><description>Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the decision intractable for humans and computationally challenging for computer algorithms. Even heuristic-based algorithms can be impractical in real systems. In this paper, we present an advanced tool that uses the query optimizer itself to both suggest and evaluate candidate MVs and indexes, and a simple, practical, and effective algorithm for rapidly finding good solutions even for large workloads. The algorithm trades off the cost for updates and storing each MV or index against its benefit to queries in the workload. The tool autonomically captures the workload, database, and system information, optionally permits sampling of candidate MVs to better estimate their size, and exploits multi-query optimization to construct candidate MVs that will benefit many queries, over which their maintenance cost can then be amortized cost-effectively. We describe the design of the system and present initial experiments that confirm the quality of its results on a database and workload drawn from a real customer database.</description><identifier>ISBN: 9780769521145</identifier><identifier>ISBN: 0769521142</identifier><identifier>DOI: 10.1109/ICAC.2004.1301362</identifier><language>eng</language><publisher>IEEE</publisher><subject>Cost function ; Database systems ; Heuristic algorithms ; Humans ; Indexes ; Information management ; Query processing ; Sampling methods ; Storage automation</subject><ispartof>International Conference on Autonomic Computing, 2004. Proceedings, 2004, p.180-187</ispartof><woscitedreferencessubscribed>false</woscitedreferencessubscribed></display><links><openurl>$$Topenurl_article</openurl><openurlfulltext>$$Topenurlfull_article</openurlfulltext><thumbnail>$$Tsyndetics_thumb_exl</thumbnail><linktohtml>$$Uhttps://ieeexplore.ieee.org/document/1301362$$EHTML$$P50$$Gieee$$H</linktohtml><link.rule.ids>309,310,780,784,789,790,2058,4050,4051,27925,54920</link.rule.ids><linktorsrc>$$Uhttps://ieeexplore.ieee.org/document/1301362$$EView_record_in_IEEE$$FView_record_in_$$GIEEE</linktorsrc></links><search><creatorcontrib>Zilio, D.C.</creatorcontrib><creatorcontrib>Zuzarte, C.</creatorcontrib><creatorcontrib>Lightstone, S.</creatorcontrib><creatorcontrib>Wenbin Ma</creatorcontrib><creatorcontrib>Lohman, G.M.</creatorcontrib><creatorcontrib>Cochrane, R.J.</creatorcontrib><creatorcontrib>Pirahesh, H.</creatorcontrib><creatorcontrib>Colby, L.</creatorcontrib><creatorcontrib>Gryz, J.</creatorcontrib><creatorcontrib>Alton, E.</creatorcontrib><creatorcontrib>Valentin, G.</creatorcontrib><title>Recommending materialized views and indexes with the IBM DB2 design advisor</title><title>International Conference on Autonomic Computing, 2004. Proceedings</title><addtitle>ICAC</addtitle><description>Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the decision intractable for humans and computationally challenging for computer algorithms. Even heuristic-based algorithms can be impractical in real systems. In this paper, we present an advanced tool that uses the query optimizer itself to both suggest and evaluate candidate MVs and indexes, and a simple, practical, and effective algorithm for rapidly finding good solutions even for large workloads. The algorithm trades off the cost for updates and storing each MV or index against its benefit to queries in the workload. The tool autonomically captures the workload, database, and system information, optionally permits sampling of candidate MVs to better estimate their size, and exploits multi-query optimization to construct candidate MVs that will benefit many queries, over which their maintenance cost can then be amortized cost-effectively. We describe the design of the system and present initial experiments that confirm the quality of its results on a database and workload drawn from a real customer database.</description><subject>Cost function</subject><subject>Database systems</subject><subject>Heuristic algorithms</subject><subject>Humans</subject><subject>Indexes</subject><subject>Information management</subject><subject>Query processing</subject><subject>Sampling methods</subject><subject>Storage automation</subject><isbn>9780769521145</isbn><isbn>0769521142</isbn><fulltext>true</fulltext><rsrctype>conference_proceeding</rsrctype><creationdate>2004</creationdate><recordtype>conference_proceeding</recordtype><sourceid>6IE</sourceid><sourceid>RIE</sourceid><recordid>eNotj8tKAzEYRgMiKHUeoLjJC8yYe_Iv7XhpsSKU7ksy-aeNdKYyGVr16S3Yb3N2h_MRMuWs4pzBw6J-rCvBmKq4ZFwacUUKsI5ZA1pwrvQNKXL-ZOdJkE7YW_K2wubQddjH1G9p50cckt-nX4z0mPCUqe8jTX3Eb8z0lMYdHXdIF7N3-jQTNGJO2576eEz5MNyR69bvMxYXTsj65Xldz8vlx-s5bVkm58YShQpCBwBjldZOW-ChaTBoEG0Dpg2gJCgXowngLTfeYgiKs6gVi9FyOSH3_9qEiJuvIXV--NlcHss_gLtLLw</recordid><startdate>2004</startdate><enddate>2004</enddate><creator>Zilio, D.C.</creator><creator>Zuzarte, C.</creator><creator>Lightstone, S.</creator><creator>Wenbin Ma</creator><creator>Lohman, G.M.</creator><creator>Cochrane, R.J.</creator><creator>Pirahesh, H.</creator><creator>Colby, L.</creator><creator>Gryz, J.</creator><creator>Alton, E.</creator><creator>Valentin, G.</creator><general>IEEE</general><scope>6IE</scope><scope>6IL</scope><scope>CBEJK</scope><scope>RIE</scope><scope>RIL</scope></search><sort><creationdate>2004</creationdate><title>Recommending materialized views and indexes with the IBM DB2 design advisor</title><author>Zilio, D.C. ; Zuzarte, C. ; Lightstone, S. ; Wenbin Ma ; Lohman, G.M. ; Cochrane, R.J. ; Pirahesh, H. ; Colby, L. ; Gryz, J. ; Alton, E. ; Valentin, G.</author></sort><facets><frbrtype>5</frbrtype><frbrgroupid>cdi_FETCH-LOGICAL-i88t-e24b25b996745585791bcceb592fc96fb943948dd6b9a716a7ebb410d540dd713</frbrgroupid><rsrctype>conference_proceedings</rsrctype><prefilter>conference_proceedings</prefilter><language>eng</language><creationdate>2004</creationdate><topic>Cost function</topic><topic>Database systems</topic><topic>Heuristic algorithms</topic><topic>Humans</topic><topic>Indexes</topic><topic>Information management</topic><topic>Query processing</topic><topic>Sampling methods</topic><topic>Storage automation</topic><toplevel>online_resources</toplevel><creatorcontrib>Zilio, D.C.</creatorcontrib><creatorcontrib>Zuzarte, C.</creatorcontrib><creatorcontrib>Lightstone, S.</creatorcontrib><creatorcontrib>Wenbin Ma</creatorcontrib><creatorcontrib>Lohman, G.M.</creatorcontrib><creatorcontrib>Cochrane, R.J.</creatorcontrib><creatorcontrib>Pirahesh, H.</creatorcontrib><creatorcontrib>Colby, L.</creatorcontrib><creatorcontrib>Gryz, J.</creatorcontrib><creatorcontrib>Alton, E.</creatorcontrib><creatorcontrib>Valentin, G.</creatorcontrib><collection>IEEE Electronic Library (IEL) Conference Proceedings</collection><collection>IEEE Proceedings Order Plan All Online (POP All Online) 1998-present by volume</collection><collection>IEEE Xplore All Conference Proceedings</collection><collection>IEEE Electronic Library (IEL)</collection><collection>IEEE Proceedings Order Plans (POP All) 1998-Present</collection></facets><delivery><delcategory>Remote Search Resource</delcategory><fulltext>fulltext_linktorsrc</fulltext></delivery><addata><au>Zilio, D.C.</au><au>Zuzarte, C.</au><au>Lightstone, S.</au><au>Wenbin Ma</au><au>Lohman, G.M.</au><au>Cochrane, R.J.</au><au>Pirahesh, H.</au><au>Colby, L.</au><au>Gryz, J.</au><au>Alton, E.</au><au>Valentin, G.</au><format>book</format><genre>proceeding</genre><ristype>CONF</ristype><atitle>Recommending materialized views and indexes with the IBM DB2 design advisor</atitle><btitle>International Conference on Autonomic Computing, 2004. Proceedings</btitle><stitle>ICAC</stitle><date>2004</date><risdate>2004</risdate><spage>180</spage><epage>187</epage><pages>180-187</pages><isbn>9780769521145</isbn><isbn>0769521142</isbn><abstract>Materialized views (MVs) and indexes both significantly speed query processing in database systems, but consume disk space and need to be maintained when updates occur. Choosing the best set of MVs and indexes to create depends upon the workload, the database, and many other factors, which makes the decision intractable for humans and computationally challenging for computer algorithms. Even heuristic-based algorithms can be impractical in real systems. In this paper, we present an advanced tool that uses the query optimizer itself to both suggest and evaluate candidate MVs and indexes, and a simple, practical, and effective algorithm for rapidly finding good solutions even for large workloads. The algorithm trades off the cost for updates and storing each MV or index against its benefit to queries in the workload. The tool autonomically captures the workload, database, and system information, optionally permits sampling of candidate MVs to better estimate their size, and exploits multi-query optimization to construct candidate MVs that will benefit many queries, over which their maintenance cost can then be amortized cost-effectively. We describe the design of the system and present initial experiments that confirm the quality of its results on a database and workload drawn from a real customer database.</abstract><pub>IEEE</pub><doi>10.1109/ICAC.2004.1301362</doi><tpages>8</tpages></addata></record>
fulltext fulltext_linktorsrc
identifier ISBN: 9780769521145
ispartof International Conference on Autonomic Computing, 2004. Proceedings, 2004, p.180-187
issn
language eng
recordid cdi_ieee_primary_1301362
source IEEE Electronic Library (IEL) Conference Proceedings
subjects Cost function
Database systems
Heuristic algorithms
Humans
Indexes
Information management
Query processing
Sampling methods
Storage automation
title Recommending materialized views and indexes with the IBM DB2 design advisor
url https://sfx.bib-bvb.de/sfx_tum?ctx_ver=Z39.88-2004&ctx_enc=info:ofi/enc:UTF-8&ctx_tim=2024-12-24T08%3A26%3A04IST&url_ver=Z39.88-2004&url_ctx_fmt=infofi/fmt:kev:mtx:ctx&rfr_id=info:sid/primo.exlibrisgroup.com:primo3-Article-ieee_6IE&rft_val_fmt=info:ofi/fmt:kev:mtx:book&rft.genre=proceeding&rft.atitle=Recommending%20materialized%20views%20and%20indexes%20with%20the%20IBM%20DB2%20design%20advisor&rft.btitle=International%20Conference%20on%20Autonomic%20Computing,%202004.%20Proceedings&rft.au=Zilio,%20D.C.&rft.date=2004&rft.spage=180&rft.epage=187&rft.pages=180-187&rft.isbn=9780769521145&rft.isbn_list=0769521142&rft_id=info:doi/10.1109/ICAC.2004.1301362&rft_dat=%3Cieee_6IE%3E1301362%3C/ieee_6IE%3E%3Curl%3E%3C/url%3E&disable_directlink=true&sfx.directlink=off&sfx.report_link=0&rft_id=info:oai/&rft_id=info:pmid/&rft_ieee_id=1301362&rfr_iscdi=true