Database Reference
In-Depth Information
CNO
CITY
START
CITY,
LNAME
CUST_BASIC
CNO
STAR
T
CUST
CNO
LNAME
CUST_PRIVATE
Figure 8.24
Which performs better?
(and probably a sort). Or perhaps we should employ downward denormalization,
for example, by copying column CITY to the CUST PRIVATE table.
Before deciding to split a table,
the benefit should be quantified. The
disk space needed for
the CUST table might be 1
.
5
×
1,000,000 rows
×
1000 bytes/row
5 GB. If the monthly cost of disk space is U.S.$50 per
gigabyte, the saving in storage cost achievable by splitting the CUST table would
be less than U.S.$75 per month.
Sometimes a separate table, such as CUST DEATH, is created because some
programs are only interested in the death data. It is true that processing such a
table can be very fast, but the same benefits can normally be achieved by a
well-designed index on the CUST table.
Often a database consists of too many tables because it just seemed more
appropriate, or maybe the objects just became tables without any performance
considerations. Such errors are difficult to correct when the application is in
production. One case was recently encountered where even the ideal indexes
were unable to make operational transactions, accessing more than a dozen tables,
fast enough. The final solution that had to be developed was to synchronously
replicate some of the data to a new combined table that was accessed by the
critical transactions, while most of the programs continued to access the small
tables. Yet another application performed so poorly that it just could not be used.
The optimizer being used appeared to have problems with joins that accessed
more than 5 tables (which was quite common; in fact some accessed more than
20!). The table structure could have been made very simple, using fewer tables,
but that would have required a large rewrite effort.
When tables are designed without hardware-conscious performance aware-
ness, the following problems are likely:
=
1
.
Search WWH ::
Custom Search