Let say you have theses tables:
-Fact table T1(a,b,g,h) with pk (a)
-Dimension table T2(b,i,j,k) with pk(b)
-Fact table T3(a,b,c) with pk (a,b) , Fk(a) -> T1 and FK(b) -> T2
The table T3 can be very big ! more than 30 million rows. And even more !!!
- columns a and b are both of type integer.
- based on the vaule of b, value of c can be integer, money , char or varchar.
Major uses of this table are :
A) do lookups based on b and c columns
Select * from T where b='xxxx' and c ='yyyy'.
Select * from T where b='xxxx' and c < 9999.
B) do lookups based on a column
Select * from T where a='zzzz'
Knowing that T3 is a very big table, my question is:
what should be done, in term of modeling, in order to optimize the lookups queries described in A and B.
Thanks in advance.
Numid