Need Help optimizing SQR

I've got an SQR that's taking several hours to run, and even then, it often crashes for lack of temp table space.

The basic SQL statement in the SQR is a join between a view and two large tables, with a SUM being computed on 4 columns, grouping by 6 other columns (hey, I didn't write this... I'm just trying to fix it). Using the Cost Optimizer, the explain plan reveals a cost of over 2 trillion!

Here's the strange part. The cost of the underlying view was 585,039. Creating two indexes, I was able to reduce the cost to 3! Great, right? So I run explain plan on the original SQL statement, and it's cost goes up to 100-something trillion! What's the deal?

Does anybody have any suggestions? Any keys to optimizing  statements with SUMs and GROUP BYs? Please help!

Dean.
dmoney_at_magnus.acs.ohio-state.edu Received on Tue Aug 02 1994 - 20:14:07 CEST