[ Pobierz całość w formacie PDF ]
.To retrieve the data from your fully normalized database, you create the followingquery, which is a sample query for a fully normalized database:SELECT a.employee_name, d.manager_name, c.training_description,b.training_dateFROM employee a, employee_training b, training c, manager dWHERE a.emp_id = b.emp_idAND b.training_id = c.training_idAND a.manager_id = d.manager_idAs you can see, this simple report requires four tables to be joined.Assume thateach table contains one million rows.Can you imagine the work involved to join fourtables, each containing one million rows? You can be assured that performance willsuffer.To maximize performance, you sometimes have to step back to 2nd or 1st normalform.If you denormalize your data into a single table, you can use the followingquery, which is a sample query for a denormalized database:SELECT employee_name, manager_name, training_description,training_dateFROM training_summaryAd-Hoc ReportingAnother reason to denormalize a database is to simplify ad-hoc reporting.Ad-hocreporting is the unstructured reporting and querying performed by end users.Endusers are often confused when they have to join a significant number of tables.Toavoid the confusion, DBAs can create a special set of tables designed for ad-hocreporting.If the data is used for reporting and not online processing, you can avoidsome of the problems associated with a denormalized design.NOTE: Views can sometimes be used as an alternative to denormalization.Viewscan present your data in a denormalized manner, which can simplify ad-hoc reporting.Denormalization TechniquesFollowing is a brief summary of the various techniques you can use to denormalizea database:Duplicate data: Duplicate data can reduce the number of joins requiredto process a query, thus reducing CPU usage and disk I/O.Summary data: Summary data can provide improved query performance by reducingor eliminating the steps required to summarize your data.Horizontal partitioning: Horizontal partitioning is the splitting of atable into two separate tables at the record level, thus reducing the number of rowsper table (see Figure 20.16).Figure 20.16.Horizontal partitioning.Vertical partitioning: Vertical partitioning is the splitting of a tableinto two separate tables at the column level, thus reducing the number of columnsper table (see Figure 20.17).Figure 20.17.Vertical partitioning.Between the LinesFollowing are important notes to remember when designing databases:Strive for 3rd normal form to maximize data consistency and minimize update anomalies.When a significant number of tables must be joined to process a query, you maywant to selectively denormalize the database to improve performance.SummaryYou can gain tangible benefits by understanding and following the rules of normalization.When a normalized design is not feasible, you should selectively denormalize.Keepin mind the concepts discussed in this chapter when you read the nextchapter--your normalization strategy might influence your indexing strategy.DISCLAIMERTo order books from QUE, call us at 800-716-0044or 317-361-5400.For comments or technical support for our books and software, select Talk to Us.© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and SchusterCompany
[ Pobierz całość w formacie PDF ]