How would you tune this ?

I have the following statement:
> SELECT
> SUM(A1.mMarketVal), SUM(A1.mIntPay), Min(A1.MarketValCur),
> Max(A1.MarketValCur), Min(A1.IntPayCur), Max(A1.IntPayCur),
> C3.seType, C3.Ref, C3.Purpose
> FROM table1 A1, table2 A2, table3 C3
> WHERE A2.lCntrId = 199334
> AND A1.lAstId = A2.lAstId
> AND A2.lCntrId = C3.lCntrId
> GROUP BY C3.seType,
> C3.Ref, C3.Purpose
> /

TABLE1 contains 182873 records

  "  2    "     165147   "
  "  3    "     41       "

The schema is analyzed, all usable indexes are used (seen in EXPLAIN PLAN), Oracle 8.1.7.1 on Sun Solaris. The value that determines the final volume of figures to be processed is "A2.lCtnrId = 199334": there are 35 different values in this field, with unequal breakdown: the 1rst ones appear between 10 to 500 times, then you have 9 values appearing about 1200 to 1500 times, then 6 values between 3500 and 8500 times, then the last 3 values appearing 11500, 28000 and 81500 times in the table (but the overall COUNT of all joins in this query gives about 1500 to 2000 rows to process when executed with the value appearing 81500 times).

Depending on the value used, this query takes 25-30 up to more than 60 seconds. I'd like to know whether there are means to improve it (with hints, of by playing with sort_area_size etc.).

What would you recommend ?
Thanks a lot.
Regards,
Seb Received on Thu Oct 17 2002 - 02:51:24 CDT