Re: Another SQL Query Question

Kim Johnson (kim_at_medicus.com) wrote:
: We're getting inconsistent results from a query that "used to work".
: We have (nearly) identical databases on two systems: both Sparc 10's
: running SunOs 4.1.3 and Oracle 7.0.12. The application tables
: and indexes are configured the same between the two databases, with
: the only difference (that we're aware of) between them being number
: of rows. The query in question suddenly stopped working on the
: larger database recently. No structural changes had been made
: prior to the problem occuring.
 

: Here's a slightly revised version of the query:
: select last_name, age, room, bed
: from patient, location, pt_aux
: where patient.location_id = location.id
: and pt_aux.id = patient.id
: and patient.active_flg = 1
: and patient.status_flg = 3
 

: ("patient" and "location" are tables, each with a primary key constraint
: defined on an "id" field; "pt_aux" is a view defined as a select on
: the patient table.)

Are you using cost-based optimization on either database? I seem to recall that there are some problems with some joins using the cost-based optimizer in 7.0.12. See if forcing rule-based optimization helps at all (ALTER SESSION etc.).

Robert Sechrest
LMSC Opinions expressed are my own, not my employer's. Received on Wed Dec 08 1993 - 17:55:17 CET