To sum up, here are the
solutions I thought of:
- Partition or subpartition the fact table on
STATE#. (preferred option)
- Create a summary table with partitioning or
subpartitoning on STATE#. (uses most space and slows
data load, but very flexible and powerful)
- Create a function-based index on fact to perform
the lookup, and query that value instead. (a bit
flaky, but it works without major system impact)
- Rebuild the fact table based on the STATE_NAME
instead. (still limited in multi-level hierarchies)
