We have a fact table which has 320 million records, we join the fact table using the time dimension to display monetary values on reports.
We decided to partition the fact table based on time_key_id column in fact table.
When we directly limit the time_key_id column in fact table, it return data in few seconds. But joining the table with time dimension, it takes some minutes to return results.
Is this the scenairo in db2, why it takes some time to return result while joining with time dimension.
The time_key_id has index on both time dimension and also in fact table.