Re: need help with query - too many physical reads

Without seeing the execution plans, it is difficult to say what might be the difference between the implementations in 8.05 and 9.2, but I suggest that a more efficient rendering of the query in either case would be:

 SELECT /*+ ORDERED INDEX(c idx1) */ to_char(c.transdate,'DD-MON-YY')  ,....
 FROM portfolios a, stocks b, data c, bonds d  where a.id = 'xyz'

  and     a.bond_id = b.bond_id 
  and     b.name in ('ABC','XYZ')
  and     ...
  and     (b.bond_id, c.transdate) in
     (select   bond_id, max(transdate) 
        from    data 
        where  ...
        group by bond_id)

Notes:
1) With this construction, only one aggregation pass has to be done through the DATA table instead of one per value of bond_id 2) Make sure you have an index on DATA (bond_id, transdate) 3) Since it may be that the optimizer has changed between 8.05 and 9.2, try omitting the optimizer hints and see whether the later version has improved the default access path.

HTH Chrysalis

<vissuyk_at_yahoo.com> wrote:

> Hi All,
>
> We have moved to 92 from 805 and one of our queries is running
> extremely slow in 92. When I looked at the auto trace, it is doing
> 18000 physical reads where as in 805 it is doing less than 1000
> physical reads to pull the data.
>
> In 805, we had no partitions. In 92 we added partitions. The data
> table (biggest table in our schema) has about 8 millions rows and
> other tables are few thousand rows each. The memory settings have been
> copied form 806 to 92.
>
> In 92 we have partitioned the table but not the index. Does it matter
> ?. Why would it end up doing up so many phys reads ?.
>
> Below is the query. The subquery tries to find the max of transdate
> (latest xn) and the outer query applies that and some other criteria
> in choosing rows. The plans look different. The 92 plan has CARTESIAN
> JOIN where as 805 doesn't show that. Can any one help.
>
> Thanks a million,
>
> Vissu
>
> SELECT /*+ ORDERED INDEX(c idx1) */ to_char(c.transdate,'DD-MON-YY')
> ,....
> FROM portfolios a, stocks b, data c, bonds d
> where
> a.id = 'xyz'
> and
> a.bond_id = b.bond_id
> and
> b.name in ('ABC','XYZ')
> and
> C =
> (
> select /*+ INDEX(data idx2) */ max(transdata)
> from data
> where
> bond_id = b.bond_id
> and ...
> )
> ORDER BY upper(d.name) asc ;
Received on Mon Aug 12 2002 - 11:05:12 CEST