2011-11-14a-DatabaseTuning [Compatibility Mode]

2011-11-14a-DatabaseTuning [Compatibility Mode] - Database...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 1 Database Tuning Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 2 Overview You have created an ER diagram, generated relations and populated them but performance is terrible! What are possible techniques? –Ind ice s – Clustering – Schema changes (denormalization, etc.) – Rewriting queries! Key is to understand the workload Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 3 Understanding the Workload For each query in the workload: Which relations does it access? Which attributes are retrieved? Which attributes are involved in selection/join conditions? How selective are these conditions likely to be? For each update in the workload: Which attributes are involved in selection/join conditions? How selective are these conditions likely to be? The type of update (INSERT/DELETE/UPDATE), and the attributes that are affected How important is a query/update? Frequent, long-running queries are usually the most important to optimize
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 4 Indices and Clustering: Decisions to Make What indexes should we create? Which relations should have indexes? What field(s) should be the search key? Should we build several indexes? For each index, what kind of an index should it be? Clustered? Hash/tree? Need to apply your knowledge of indexing Also need to make sure that optimizer uses the indices! (including index-only plans) Need to apply your knowledge of optimizers! Database Management Systems, 2 nd Edition. R. Ramakrishnan and J. Gehrke 5 Choice of Indexes One approach – Consider the most important queries in turn – Consider the best plan using the current indexes, and see if a better plan is possible with an additional index – If so, create the additional index – “Greedy” Before creating an index, must also consider the impact on updates in the workload!
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/07/2011 for the course CS 4410 taught by Professor Vollset during the Spring '07 term at Cornell University (Engineering School).

Page1 / 6

2011-11-14a-DatabaseTuning [Compatibility Mode] - Database...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online