RelationalDBDesign RelationalDBDesign 

Performance Tuning   «Prev  Next»
Lesson 5Implementing logical database performance design
ObjectiveDescribe Characteristics of high-performing database

Logical Database Design

Compare Database Design using Real-life example

To illustrate the implications that logical design has for performance, let's consider a third normal form database design for placing orders for products. Management wants to know the counts of "State flower" for all orders that have been placed for "sunflower seeds." The output would look something like this:

State_flower   count(*)
------------   --------
Begonia        433,233
Pansy          121,455
Tulip           44,233
Zenia            3,245

The Slide Show below will allow you to compare how different database designs will result in different SQL statements.
Database Design 1
1) Database Design 1
Database Design 2
2) Database Design 2
Database Design 3
3) Database Design 3
Database Design 4
4) Database Design 4
The extreme in redundant database design is commonly used with data warehouse tables. See the paragraph below for more information.

Data warehouse table design

In data warehouses, "wide" tables are created that contain a huge amount of redundant information. Because of the huge amount of data redundancy, these wide tables are very cumbersome to update, but they can be queried with remarkable speed. In our example, all of the other tables could be combined into the QUANTITY table, creating a single, very wide table that contains all of the order, customer, salesperson, and item information. From an Oracle perspective, a single-row fetch would access all of the line item information for each order, and complex queries would no longer require table joins.
Now that we have reviewed logical design, let's look at how physical design will impact performance.

Denormalizing Database - Exercise

Click the exercise link below to try your hand at redesigning a database to optimize performance.
Denormalizing Database - Exercise