Re: need help with query - too many physical reads

Are things analyzed? Also try it without the order by to see if it makes a difference. You should use bind variables. Jim
"Vissu" <vissuyk_at_yahoo.com> wrote in message news:2bedd6a7.0208111921.241effb7_at_posting.google.com...
> 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.transdate =
> (
> 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 - 06:52:18 CEST