need help with query - too many physical reads

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 - 05:21:32 CEST