Physical Design   «Prev  Next»

Lesson 2Physical Design
ObjectiveDescribe Physical Database Design in terms of optimizing Database Performance

Physical Database Design

How a Computer stores Data

A computer's hard disk is more than a collection of 1s and 0s. To manage the data on a hard disk effectively, a computer's operating system (such as Windows or Unix) divides the disk into blocks [1]. Each block can hold a certain amount of information. If a file is too large to fit onto a single block, the computer finds the next empty block, continues writing the file there, and repeats the process until the file is completely written to the disk. The following illustration shows how a hard disk might be divided among several files.

Hard disk that is divided among several files.
A block represents the smallest area that can be addressed on a magnetic disk.

Note the file titled word1.doc is written on five blocks; the file me.gif is written on five blocks; and the file resume.doc is written on seven blocks.
Any given block can hold information from only a single file.

Adding and modifying Files

If you have created and modified a number of files, it is unlikely that all of the data for a particular file will be stored on contiguous blocks. Suppose you create a document using a word processor, save a graphic from a World Wide Web site, and then add a page to the word processor document. Because the operating system writes files to open blocks in the order that the files are created and modified, the physical layout of the files on the disk would be similar to the layout in the following graphic.

Physical layout of the files on disk might be similar to the layout above.
To read data from resume.doc the computer will need to jump between the two blocks

The next time you open the word processor document, the computer will consult its internal directory of stored files, read the data from the first set of blocks, move to the second area of the disk, and then read the second set of blocks. The more often a computer needs to move to another area of the disk to read part of a file, the longer it takes to read the file.

Improving performance using physical design

Most RDBMS's are able to rearrange files on a hard disk to store records from a table in contiguous blocks. You can go even farther and use other techniques to improve the performance of your database, including:
  1. Indexing
  2. Clustering
  3. Partitioning

The following Slideshow offers a quick overview of these techniques. The remaining lessons in this module examine each in greater depth and list their advantages and disadvantages.

Physical Design Overview
The next lesson introduces indexing.

[1]block: The smallest area that can be addressed on a magnetic disk.