Avoid all-rows scan in simple SQL statement (V2R4.1)

[MSIsam]

I have a SQL query generated by Microstrategy and want to improve performance.
Sample Code:

SELECT 
  WEEK_DIM.WEEK_DESC, 
  FACT.SALES
FROM 
  FACT,
  WEEK_DIM
WHERE 
  FACT.WEEK = WEEK_DIM.WEEK
AND [COLOR=blue]WEEK.LAST_WEEK_IND = 1[/color]

This query is doing an all rows scan of the fact table without first filtering on the week specified in the week dimension table. I have a somewhat limited knowlege of query plans but I would like to know if there is any tuning that could make the query perform in the same way as the one below?

CODE
SELECT 
  WEEK_DIM.WEEK_DESC, 
  FACT.SALES
FROM 
  FACT,
  WEEK_DIM
WHERE 
  FACT.WEEK = WEEK_DIM.WEEK
  AND [COLOR=blue]FACT.WEEK = 200408[/color]

Thanks for any assistance

 

[dnoeth]

Could you post more info?
- Defined Indexes
- Collected statistics
- Row counts
- Explains of both queries

Dieter

 

[rrrkrishnan]

Try this, but as Dieter says ,more info is needed to understand and improve the execution plan.

SELECT
WEEK_DIM.WEEK_DESC,
FACT.SALES
FROM
FACT,
(SELECT * FROM WEEK_DIM WHERE LAST_WEEK_IND = 1) WEEK_DIM
WHERE
FACT.WEEK = WEEK_DIM.WEEK

 

[BillDHS]

I would guess it does a full table scan because it is not the primary index and the number of distinct values is small. Is the value 1 for last week and 0 for not last week? If Teradata believes it needs to do I/O on a majority of the table blocks anyway, it will start a full table scan that can be faster than a secondary index/table row retrieve which does multiple I/O's.

How many distinct values are there for last_week_ind?

 

[MSIsam]

Week Dim table has ~800 rows and the LAST_WK_IND = 1 has one occurence (other rows are all = 0).

 

[JaC74]

What is the index on both the Fact and DIM tables?
When was the last time Stats were collected?
Are stats being collected on the right columns?

I deal with MicroStrategy / Teradata performance issues on a daily basis and there are several tricks that will make MicroStrategy spit out the SQL needed to read the table via index.