My situation is as follows. I have several big SQL queries in a data
warehouse using 1 big fact tables and 10 dimension tables. The queries
join all of them together but the optimizer doesn't care which
dimension table it joins first (we ran runstats on all of them but the
dimensions look a lot alike... about same order of rows, ...). I know
it should first join on 1 specific dimension if it's in the query
since that dimension will discard most of the facts.
I can't change the queries as they are being generated by an
off-the-shelf tool. I was thinking of manipulating the runstats data
manually (db2look -m ..., and then applying manual changes) but can
anyone enlighten me how to "fix" the runstats information to give this
table more preference by the optimizer.
We're running v7.2 of IBM DB2 on HP-UX. And I know it's not a nice
solution but if it makes the difference between a runtime of 1 minute
and 8 hours... I'm willing to do some dirty tricks (I played with the
SQL query manually by adding some coalesce's and when the optimizer
goes over my 1 dimension the query is blazingly fast).
Regards,
Jan