Database Reference
In-Depth Information
Chapter 16
Optimizing the Physical Design
During the translation from the logical design to the physical design, you must make four kinds of decisions. First,
for each table, you have to decide not only whether you should use a heap table, a cluster, or an index-organized
table, but also whether it has to be partitioned. Second, you must consider whether you should utilize redundant
access structures such as indexes and materialized views. Third, you have to decide how to implement the constraints
(not
whether
you have to implement them). Fourth, you have to decide how data will be stored in blocks, including
the order of the columns, what datatypes are to be used, how many rows per block should be stored, or whether
compression should be activated. This chapter focuses on the fourth topic only. For information about the others,
especially the first two, refer to Chapters 13, 14, and 15.
The aim of this chapter is to explain why the optimization of the physical design shouldn't be seen as a fine-
tuning activity but as a basic optimization technique. The chapter starts by discussing why choosing the correct
column order and the correct datatype is essential. It continues by explaining what row migration and row chaining
are, how to identify problems related to them, and how to avoid row migration and row chaining in the first place.
Then, it describes a common performance issue experienced by systems with a high workload: block contention.
Finally, it describes how to take advantage of data compression to improve performance.
Optimal Column Order
Little care is generally taken to find the optimal column order for a table. Depending on the situation, this might
have no impact at all or may cause a significant overhead. To understand what situations this might cause significant
overhead in, it's essential to describe how the database engine stores rows into blocks.
A row stored into a block has a very simple format (see Figure
16-1
). First, there's a header (H) recording some
properties about the row itself, such as whether it's locked or how many columns it contains. Then, there are the
columns. Because every column might have a different size, each of them consists of two parts. The first is the length
(L
n
) of the data. The second is the data (D
n
) itself.
Figure 16-1.
Format of a row stored in a database block (H = row header, Ln = length of column n, Dn = data of column n)
