Extra Index Key Required

[PAULSGS]

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?

 

[blom0344]

Paul, this is a case that is not really BO related. You may have more succes with database-related forums I guess.

My question would be (as I work with DB2 as well); did you try the performance of a union query? If the original queries use the appropriate index then a union query would be about twice as slow , but still workable.

Is the facttable part of a datawarehouse? Do you, by chance control the ETL process? There may be some possibilities with clustered indices reducing table-scans..

Ties Blom
Information analyst

 

[sh4kil]

How about concatenating FROM and TO together, eg:

FROM ||'----'|| TO

'London Heathrow' ||'----'|| 'Paris CDG'

or

'Paris CDG' ||'----'|| 'London Heathrow'

You can then look for:

Destination Matches Pattern '%London Heathrow%'

Any quicker?