We have a query & reporting environment based upon a star schema accessed by the Business Objects WebIntelligence Reporting tool.
I have a fact table of journies. The records have columns for departure and arrival points both as numerical ids and as 3 letter abbreviations.
Dimension tables hang off of these two columns and users can retrieve details of journies "TO" a destination or "FROM" a start point. Single column indexes exist against both of these columns and by examining the execution plan I can see that the query is quite efficient.
The problem comes when users want to see journies "TO" or "FROM" a single place, for example all journies leaving or arriving at London Heathrow.
For this I have built a third dimension table which has an "OR" in the join from the Dimension table to the Departure column "OR" the Arrival column, so the database has to scan the fact table twice and performance drops off.
Is there any way that I can build a third column in my fact table to create another index which with a single scan can retrieve, say London Heathrow either as a start point or and end point of a journey?
Function based indexes are not an option as the database is DB2.
I have already tried a double column index but these are ineffective in "OR" cases.
I have also tried building an intermediate table between the third "TO or FROM" dimension to the fact table. This intermediate table contains the content of the TO & FROM columns of the FACT table, so it has twice as many records, but this doesn't seem to offer the performance break-through that I'm looking for.
Has anyone got any ideas?
I have a fact table of journies. The records have columns for departure and arrival points both as numerical ids and as 3 letter abbreviations.
Dimension tables hang off of these two columns and users can retrieve details of journies "TO" a destination or "FROM" a start point. Single column indexes exist against both of these columns and by examining the execution plan I can see that the query is quite efficient.
The problem comes when users want to see journies "TO" or "FROM" a single place, for example all journies leaving or arriving at London Heathrow.
For this I have built a third dimension table which has an "OR" in the join from the Dimension table to the Departure column "OR" the Arrival column, so the database has to scan the fact table twice and performance drops off.
Is there any way that I can build a third column in my fact table to create another index which with a single scan can retrieve, say London Heathrow either as a start point or and end point of a journey?
Function based indexes are not an option as the database is DB2.
I have already tried a double column index but these are ineffective in "OR" cases.
I have also tried building an intermediate table between the third "TO or FROM" dimension to the fact table. This intermediate table contains the content of the TO & FROM columns of the FACT table, so it has twice as many records, but this doesn't seem to offer the performance break-through that I'm looking for.
Has anyone got any ideas?
