SQL/MX Release 2.0 Best Practices

Possible Implementations 33
time, starting with the same partition-number value. This occurs only when the fact table is not the first
outer table of the query.
Other Benefits of Partitioning
The partition table has other benefits. If the partition table is selected as the outer table of the join, the
partition table determines the number of ESPs the query uses. By creating the partition table using one
partition for each processor, and by structuring the partition table so that the rows within each partition
align with the corresponding fact table rows, the join performed by the ESPs accesses corresponding
partitions within the same processor. This method provides efficient join-performance management of the
ESPs.
New data loaded into the fact table can occur in parallel. Purge processing is similar to hash partitioning,
but more efficient. Update processing is similar to sequential partitioning. However, MDAM query
technology is used to locate the correct partition because this value is unknown to the application.
Database maintenance is simplified because the amount of data directed to a set of partitions is
controlled through the loading application, and each partition fills at the same rate. Online reloads are
not needed as often since partitions do not become easily fragmented. Secondary index creation of
hash partitioning is similar to that of sequential partitioning. The backup requirements are similar to those
of hash partitioning, because all partitions are affected by data loading.
Hash partitioning offers very flexible implementation alternatives. You could distribute data across all
partitions, or—when the data volume is relatively small—only a subset of them. This method is also
adaptable to growth in the database. If the existing database is evenly divided across the processors,
you can provide additional capacity by adding another set of disk drives (also balanced across
processors), creating another set of partitions, and distributing new data over these new partitions. This
method eliminates the need to rebalance the entire database.
Fact Table Partitioning Summary
Each partitioning method has advantages and disadvantages. You must have clearly defined objectives
and requirements before finalizing your choice. You should have well-defined queries that accurately
represent the business concerns of the users. Engage your users in this decision process; no IT department
understands the business requirements better than the users do. If possible, construct a full-scale
prototype and study the resulting query plans and performance characteristics to validate that the
design will work as intended. The complexity of some database designs makes predicting query response
time very difficult.
If a full-scale environment is not available, there are several alternatives. You could implant the SQL
catalog and schema with production statistics so that the subsequent analysis of query plans is more
accurately represented. This action can be done without loading any data at all. Then you can make an
analysis of the query plans.
The goal is to validate that the physical design is used as intended. Once the query-plan analysis confirms
the design, load some data into the tables to get an indication of what the response time will be, and
how well the system will be used. Changing the physical design at this stage is much easier than
changing the physical design after the application has been implemented.
You may need to do several iterations before you find an effective design, but the effort can be
worthwhile, especially when done early in the project. Be aware, however, that no amount of effort can
substitute for well-defined objectives and requirements, and for including the end users in decision
making.
Testing the Results for SQL/MX Tables or Creating Artificial
Statistics
To test the results of updating statistics:
1. Prepare a sample query from your application. Consider using a commonly used query from your
application.
2. Use EXPLAIN to obtain the cost information for your query.