Physical Storage Considerations
Go Up to Developing the Logical Model
Planning the storage of tables and indexes is best done during the physical design stage in order to improve performance and to streamline data administration tasks. When planning physical storage, carefully consider both the placement and size of tables and indexes.
The performance of almost all database applications is I/O bound. To improve I/O throughput, you should physically separate tables that are frequently joined together. You should also separate tables from their indexes. The objective is to have the database read or write data in parallel as much as possible.
Two key concerns of every database administrator are free space management and data fragmentation. If you do not properly plan for the volume and growth of your tables and indexes, these two administrative issues could severely impact system availability and performance. Therefore, when designing your physical model, you should consider the initial extent size and logical partition size.
As a starting point, you should estimate the size of each table and its indexes based on a projected row count. For databases that let you specify initial extent sizes, such as Oracle, you should set the initial extent size to the estimated size in order to avoid data fragmentation as the table grows. By keeping tables within a single extent, you can decrease data access times and avoid reorganizing tables.
Once you have determined the placement and sizes of the tables and indexes in your database, you can estimate the total size requirements of the database. This should help you avoid running out of free space for your database.
The following sections describe how you can optimally store and partition tables and indexes through the options available in the table and index editors for the specific database platforms that support these options.
- Defining Table Storage
- Distributing Columns for IBM DB2 for LUW 9.x (and later versions)
- Distributing Columns for GreenPlum
- Distributing Columns for Netezza
- Organizing Columns for Netezza
- Partitioning a Table
- Defining Index Storage
- Partitioning a Table Index