Usenet.com

www.Usenet.com

Group Index

Comp Thread Archive from Usenet.com

<-- __Chronological__ --> <-- __Thread__ -->

Same SQL in 9i vs 7.3.4.5 takes long time



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__ -->


Usenet.com



Please check out one of the premium Usenet Newsgroup Service Providers below for access to Usenet.