How do i improve performance of this fact table and this simple query?

[freeka]

Hi there

I have a FACT table called TRANSACTIONS_FACT which contains calculations on accounts such as amount_debited, amount_credited.

I also have a DATE_DIMENSION table.

In my TRANSACTIONS_FACT table, I have a FK link to the DATE_DIMENSION table via the column DATE_KEY. In the fact table this gives the date key of the date on which the transaction occurred.

So, my query, is this:

"Select the transactions from the fact table that occurred between date A and date B"

However, in order to do this according to Dimensional Modelling classes I went to, I need to do a join between the dimension and fact table using the DATE_DIMENSION's primary key and then have the "DATE_DIMENSION.DATE BETWEEN A AND B".

My problem is that this query takes 11 minutes to run whereas if I simply had the date in the fact table, it takes under 1 second.

Am I doing something wrong here or is this simply how the world of data warehousing is?

I have a unique index on the date key in the fact& dimension table and I have a non-unique BITMAP index on the date field in the DATE_DIMENSION.


Any thoughts, suggestions, comments would be most welcome.

 

[freeka]

hi all

I solved this.

For those interested in knowing:

I created bitmap indexes on the FK column in the fact table. There are UK indexes on the date value in the dimension table.

I also created a bitmap join index as well just to be on the safe side but I think that was overkill.

The query now takes under 1 second.

 

[Hans63]

Hi Freeka,

good to see you found the solution yourself. Using Bitmap indexes on all FK columns in the fact table is what oracle suggests. And what I have found to work well.