
www.Usenet.com
| <-- __Chronological__ --> | <-- __Thread__ --> |
Doing application testing for an upgrade to Oracle 9.2 from 7.3.4.5. One
query that finishes almost instantaneously on 7.3.4.5 takes at least a
couple of minutes on 9.2.
A similar query would be:
select a.cust_no
,a.first_name
,a.last_name
,b.item_no
,b.asn_dt_tm
,c.plan_no
,c.asn_dt_tm
from custs a
items b
plans c
where a.cust_loc='1'
and a.cust_no='12345'
and b.id=a.id
and c.id=a.id
and b.asn_dt_tm = (select max(asn_dt_tm)
from items b1
where b1.id=b.id)
and c.asn_dt_tm = (select max(asn_dt_tm)
from plans c1
where c1.id=c.id)
;
The PK for custs is id. There is an index on the cust_loc/cust_no fields.
The PKs for the items and plans table are a combo on the id,asn_dt_tm
fields. asn_dt_tm is a date field. When I comment out the two subquery
parts of the where clause, the query zips back on the 9i. When I just run
the select statement in the subquery, it comes right back as well.
Any thoughts as to why this might be taking a while on 9i? This is a
database that I migrated in place from a copy of the 7.3.4.5 database.
| <-- __Chronological__ --> | <-- __Thread__ --> |