Unanswered: non-running sql on starschema
Consider the following SQL:
sum(BIKE_SALES_DAILY.QTY * BIKE_SALES_DAILY.NET_PRICE * BIKE_SALES_DAILY.DISC_FACTOR * BIKE_SALES_DAILY.EXCHANGE_RATE),
( 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 )
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
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?
Senior Application Developer BI
SQL, SQL server