SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 408

What about the statistics for calculating the costs? Where are they stored?
Typically, the data dictionary or system catalog stores the following statistics to be
used for cost calculation:
Data cardinality. Number of rows for each relational table.
Data volume. Number of data blocks for each relational table.
Index cardinality. Number of distinct values for each index.
Index height. Number of nonleaf levels in each B-tree index.
Index range. Current minimum and maximum values of the key for each index.
As you know, day-to-day database transactions will be affecting these statistics
for each relation continually. The number of rows of a particular relation is likely
to change, as are other statistics about the relation. As the statistics change, so does
the calculation of the access costs for queries on given relations. If the statistics for
a given relation have to be changed by every relevant transaction, such an arrange-
ment will slow the transactions down. Therefore, the general practice is to run
special utility jobs for updating the statistics. When the statistics are updated, any
affected, compiled queries must be reoptimized
To examine the principles of cost-based optimization, let us consider a query
discussed above.
SELECT SERVICE.ServiceCode, ServiceDesc
FROM SERVICE, BILLING
WHERE SERVICE.ServiceCode = BILLING.ServiceCode and
PatientNo = 224466
Figure 13-19 presents two versions of the query tree for executing this query.
Note the differences between the two tree versions. Even with a cursory examina-
tion, the cost of running version 1 of the query is seen to be much larger because
of the large product operations. Cost-based optimizers calculate the costs for per-
forming each operation as you move up the query tree. The cost for each type of
operation depends on a number of factors including the types of indexes available.
Just note how the query optimizer first comes up with a reasonable set of query tree
options and then decides on the optimal tree after calculating the costs of execut-
ing each tree version. The details of how these cost calculations are performed are
beyond the scope of our discussion.
DATABASE SYSTEM DEPLOYMENT
Let us trace back and find our place in the database development life cycle. The life
cycle begins with planning and feasibility study. Then you gather the information
requirements and produce the requirements definition document. Next, in the
design phase, you complete the logical design followed by the physical design of the