I need help guys. I am working on a data warehouse that has a star schema.
the fact table is partition and i have a couple of dimension table some which have a lot of data (4 million or less) and some which are very small. i tried to optimize some query that was taking long so i created an index based on the where clause of the query. still the query was taking about 10 secs to run since it was doing sum and group by. when i collect stats (20%) the query ran in 2 secs. this is not bad.

i started quering a different query and i notice that this query uses some of the columns in the where clause that use to create the index. this query is a join of 1 fact and 4 dimensions table where each dimension table contain a primary key and it is use to join with the fact table. what i notice is that the query is doing full table scan on all tables. the index is not use. i even create new index and collect stats and still the query don't use the index. when i delete stats on fact table, then the query make use of the index i create and it takes 6 seconds which is much faster than before.

if i collect stats, it is good for some query but not good for others. the same thing if i don't collect stats. my question is why is this happening?
what do i need to check to fix this problem so that the index is use for both query when i collect stats? any solutions, ideas?