442 Database Management

442 Database Management - Physical Database Design Overview...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Physical Database Design Overview After ER design, schema refinement, and the definition of views, we have the conceptual and external schemas for our database. The next step is to choose indexes, make clustering decisions, and to refine the conceptual and external schemas (if necessary) to meet performance goals. We must begin by understanding the workload : The most important queries and how often they arise. The most important updates and how often they arise. The desired performance for these queries and updates. 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? 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 ... Index Selection for Joins When considering a join condition: Hash index on inner is very good for Index Nested Loops.  Should be clustered if join column is not key for inner, and inner tuples need to be retrieved. Clustered B+ tree on join column(s) good for Sort-Merge. Example 1 Hash index on D.dname supports Toy selection. Given this, index on D.dno is not needed. Hash index on E.dno allows us to get matching (inner) Emp tuples for each selected (outer) Dept tuple. What if WHERE included: `` ... AND E.age=25 ? Could retrieve Emp tuples using index on E.age , then join with Dept tuples satisfying dname selection. Comparable to strategy that used E.dno index. So, if E.age index is already created, this query provides much less motivation for adding an E.dno index. SELECT E.ename, D.mgr FROM Emp E, Dept D WHERE D.dname=Toy AND E.dno=D.dno Example 2 Clearly, Emp should be the outer relation. Suggests that we build a hash index on D.dno. What index should we build on Emp? B+ tree on E.sal could be used, OR an index on E.hobby could be used. Only one of these is needed, and which is better depends upon the selectivity of the conditions....
View Full Document

Page1 / 30

442 Database Management - Physical Database Design Overview...

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

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