The following are the high-level stages in the physical design process:
- Identify all index and partitioning candidates.
- Review table demographics, then verify or modify index and partitioning choices.
- Evaluate denormalization possibilities, including dimensional views, then verify or modify index and partitioning choices.
- Evaluate derived data, then verify or modify index and partitioning choices.
- Implement the system and initiate production processing.
- Track changing demographics.
- Review indexes and partitioning periodically to ensure they are still the best choices.
- Perform appropriate EXPLAIN request modifiers periodically to track whether defined indexes are being selected for query processing by the Optimizer and whether the current partitioning is still facilitating row and column partition elimination.
- Reevaluate table use.
- Document any changes.
- Archive older versions of your findings for use in analyzing trends.