 Physical Database Design
Physical database design is the single most important factor that impacts database performance. Physical database design covers all of the design features that relate to the physical structure of the database such as datatype selection, table normalization and denormalization, indexes, materialized views, data clustering, multidimensional data clustering, table (range) partitioning, and database (hash) partitioning.
Good physical database design reduces hardware resource utilization (I/O, CPU, and network) and improves your administrative efficiency. This, in turn, can help you achieve the following potential benefits to your business:
- Increased performance of applications that use the database, resulting in better response times and higher end-user satisfaction
- Reduced IT administrative costs, giving you the ability to manage a wider scope of databases and respond quicker to changes in application requirements
- Reduced IT hardware costs
- Improved backup and recovery elapsed time
Figure 1. Illustration of a physical database system
Figure 1 shows an illustration of a physical database system. The three heavy dark-boxed vertical rectangles indicate three distinct database instances. All other square or rectangular boxes represent storage blocks on disk. All symbols represent data values within the table (such as geography or month).
In this example, a table has been hash-partitioned across three instances called P1, P2, and P3. The table has been range-partitioned by month, allowing data to be easily added and deleted by month. Indirectly, this also helps with queries that have predicates by month. Data within each table has been clustered using multidimensional clustering (MDC), and this serves as a further clustering within each range partition. The rows within the table are also indexed using regular row-based (RID-based) indexes. A materialized query table (MQT) is created on the table, which includes aggregated data (such as average sales by geography), which itself has indexing and MDC.
|
