17-07-2012, 05:06 PM
Database Tuning
Database Tuning.ppt (Size: 262.5 KB / Downloads: 74)
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.
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? Dynamic/static? Dense/sparse?
Should we make changes to the conceptual schema?
Consider alternative normalized schemas? (Remember, there are many choices in decomposing into BCNF, etc.)
Should we ``undo’’ some decomposition steps and settle for a lower normal form? (Denormalization.)
Horizontal partitioning, replication, views ...
Issues to Consider in Index Selection
Attributes mentioned in a WHERE clause are candidates for index search keys.
Exact match condition suggests hash index.
Range query suggests tree index.
Clustering is especially useful for range queries, although it can help on equality queries as well in the presence of duplicates.
Try to choose indexes that benefit as many queries as possible. Since only one index can be clustered per relation, choose it based on important queries that would benefit the most from clustering.
Horizontal Decompositions
Our definition of decomposition, so far: Relation is replaced by a collection of relations that are projections. This is vertical decomposition. Most important case.
Sometimes, might want to replace relation by a collection of relations that are selections. This is horizontal decomposition.
Each new relation has same schema as the original, but a subset of the rows.
Collectively, new relations contain all rows of the original. Typically, the new relations are disjoint.
Summary (Tuning)
The conceptual schema should be refined by considering performance criteria and workload:
May choose 3NF or lower normal form over BCNF.
May choose among alternative decompositions into BCNF (or 3NF) based upon the workload.
May denormalize, or undo some decompositions.
May decompose a BCNF relation further!
May choose a horizontal decomposition of a relation.
Importance of dependency-preservation based upon the dependency to be preserved, and the cost of the IC check.
Can add a relation to ensure dep-preservation (for 3NF, not BCNF!); or else, can check dependency using a join.