Consider the following SQL:


SELECT
BIKE_SALES_DAILY.FISC_YEAR,
TIME_DIST_PERIOD.FISC_PERIOD,
sum(BIKE_SALES_DAILY.QTY * BIKE_SALES_DAILY.NET_PRICE * BIKE_SALES_DAILY.DISC_FACTOR * BIKE_SALES_DAILY.EXCHANGE_RATE),
BIKE_IREF01_TRANSLATION.IREF01
FROM
BIKE_SALES_DAILY,
TIME_DIST_PERIOD,
BIKE_IREF01_TRANSLATION,
BIKE_CUSTOMER,
DIM_ITEM
WHERE
( DIM_ITEM.ITEM_ID=BIKE_SALES_DAILY.ITEM )
AND ( BIKE_IREF01_TRANSLATION.IREF01=DIM_ITEM.IREF01 )
AND ( BIKE_SALES_DAILY.CUSTOMER=BIKE_CUSTOMER.CCUST )
AND ( BIKE_SALES_DAILY.FISC_YEAR=TIME_DIST_PERIOD.FISC_Y EAR AND
BIKE_SALES_DAILY.FISC_MONTH=TIME_DIST_PERIOD.FISC_ MONTH )
AND (
BIKE_CUSTOMER.CCUST BETWEEN 200000 AND 499999
AND BIKE_CUSTOMER.CSAL IN (1, 2, 3, 4, 5, 6, 4001, 4002, 4003, 4004, 4005, 4006)
AND BIKE_SALES_DAILY.FISC_YEAR >= 2003
)
GROUP BY
BIKE_SALES_DAILY.FISC_YEAR,
TIME_DIST_PERIOD.FISC_PERIOD,
BIKE_IREF01_TRANSLATION.IREF01


The facttable 'BIKE_SALES_DAILY' contains 1.5 million records and is joined to the itemmaster 'DIM_ITEM' which itself is joined to 'BIKE_IREF01_TRANSLATION' for reporting/consolidation purposes.

This SQL seems to run forever, explain plan indicates that the optimizer creates a hash join between the facttable and the itemmaster to start with.

Normally an indexed field from the dimension table 'BIKE_IREF01_TRANSLATION' would be used to fetch data for a group of products, but this SQL attempts to fetch all for two years and it does not perform.

Up till now there was no need to create additional indices other than the IREF01 field in the 'DIM_ITEM' table. Should I create an additional index for the field 'BIKE_SALES_DAILY.ITEM ' ?

Any other suggestions?