Another SQL Query Question

From: Kim Johnson <kim_at_medicus.com>
Date: Wed, 8 Dec 1993 00:40:18 GMT
Message-ID: <1993Dec8.004018.6656_at_medicus.com>

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.)

This query produces the correct results on one system. An explain plan showed that two table scans were done on the patient table, and one scan on the location table.

When run on the other database the query returns no rows, although over 200 rows should have been returned. An explain plan showed that the patient table was scanned both with its primary key index and with a table scan, and the location table was scanned.

We finally got the query working again by dropping the primary key on the patient table (a temporary solution). We also found that making any of the joins an outer join got the query working again too.   

So, any ideas on what's going on here would be appreciated. Thanks.

---

Kim Johnson
Software Engineer
Medicus Systems, Corp.
Alameda, CA Received on Wed Dec 08 1993 - 01:40:18 CET