RE: 9.2.0.4 buffer (sort)

From: Gamble, Scott <Scott.Gamble_at_cardinal.com>
Date: Tue, 13 Jul 2004 11:07:02 -0500
Message-ID: <A208F793ACBE00439E6FB2AB0E8B57D6FC9EC3@mpgexch01.cahapps.net>

The cartesian is not the problem I am comparing plans on 8.1.7.4 and = 9.2.0.4 both of which do the cartesian (which happens to be the fastest = way to do this query).

     select tril_adjustments.unid, tril_adjustments.organization_id,
       TO_CHAR(tril_adjustments.start_date, 'YYYY-MM-DD HH24:MI:SS'),
       TO_CHAR(tril_adjustments.end_date, 'YYYY-MM-DD HH24:MI:SS'),
       tril_adjustments.object_name, tril_adjustments.pricing_type_id,
       tril_adjustments.amount, tril_adjustments.low_range,
       tril_adjustments.high_range, tril_adjustments.object_type,
       tril_adjustments.range_type
     from
      tril_adjustments, TRIL_JOINS_WRK j2, TRIL_JOINS_WRK j1=20
     where
       tril_adjustments.object_name =3D j2.join_text=20
       and j2.local_id =3D :1=20
       and j2.join_number =3D :2=20
       and tril_adjustments.organization_id =3D j1.join_text=20
       and j1.local_id =3D :3=20
       and j1.join_number =3D :4=20
       and tril_adjustments.start_date <=3D :5=20
       and tril_adjustments.end_date >=3D :6=20
       order by tril_adjustments.organization_id asc

 =20
 =20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Paul Drake Sent: Tuesday, July 13, 2004 10:53 AM
To: oracle-l_at_freelists.org
Subject: Re: 9.2.0.4 buffer (sort)

• "Gamble, Scott" <Scott.Gamble_at_cardinal.com> wrote:
  > Platform HP Tru64 5.1b
  > DB version 9.2.0.4
  >=20
  > During testing of 9.2.0.4 on a test database with
  > full production data =3D
  > (fully analyzed) I have a query critical to this
  > application that is =3D
  > performing slower in the 9.2.0.4 database.=3D20
  

"merge join cartesian" is the real clue here. chances are quite good that you have a join condition missing that could provide for a better access path if explicitly stated.

Please re-examine the tables in the statement and look for other joins or filtering conditions.

I did not see the statement in your posting.

Pd

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 13 2004 - 11:03:48 CDT