Re: How would you tune this ?

Salut Sebby,

"Sebby" <sebastien.rigaud_at_b-rail.be> schrieb im Newsbeitrag news:5bc80fe2.0210162351.444ffbe2_at_posting.google.com...
> 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
> > /
>

First i will reorganize the tables as following:

FROM table3 C3, table2 A2, table1 A1, table1 A1

then i will reorg the where clause as follow:

WHERE C3.lCntrId = 199334 // constants first, smallest table first each table will be run throu only once
AND A2.lCntrId = C3.lCntrId // right side is found, left will be appended>
AND A1.AstId = A2.lAstId

hth
matthias

> 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 - 08:35:09 CDT