Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
Subject: RE: wrong returns 
Date: Tue, 20 Nov 2007 14:13:22 -0500
Message-ID: <AA29A27627F842409E1D18FB19CDCF270E119391@AABO-EXCHANGE02.bos.il.pqe>
In-Reply-To: <4743231E.2090304@tufts.edu>
Thread-Topic: wrong returns 
References: <4743231E.2090304@tufts.edu>
From: "Bobak, Mark" <Mark.Bobak@il.proquest.com>
To: <joan.hsieh@tufts.edu>,
 "oracle_l" <ORACLE-L@freelists.org>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: Mark.Bobak@il.proquest.com

Hi Joan,

Since you mentioned 10g, this is a bit of a long shot, but, in the database where you get wrong results, try setting _table_lookup_prefetch_size = 0.  (This is a static parameter, so you'll have to bounce the instance.)  If that solves the problem, check out MetaLink Note 406966.1.

-Mark


--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak@il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here. 


-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Joan Hsieh
Sent: Tuesday, November 20, 2007 1:11 PM
To: oracle_l
Subject: wrong returns 

Hi,

We have one sql statement ran in two databases, both in 10g, one returns 
as following;

ID_NUMBER  Sort_Name                                                    Club
---------- ------------------------------------------------------------ 
----------------------------------------
0000110534 SMALL,JONATHAN,A. 
Fletcher Fund President's Circle
0000110535 SMALL,CORNELIA,M. 
Fletcher Fund President's Circle


In prod database, we get the same two rows come back, but the club 
column one is "NULL", it should return with the same value as above.

ID_NUMBER  Sort_Name                                                    Club
---------- ------------------------------------------------------------ 
----------------------------------------
0000110534 SMALL,JONATHAN,A. 
Fletcher Fund President's Circle
0000110535 SMALL,CORNELIA,M.

Question: both databases returns the right rows, but one column didn't 
return the value it should be. The db ran different execution plans, 
both db have very close dataset and same optimizer parameters setting. 
colud someone can shed some light why this could be wrong...

The sql statement is;
SELECT DISTINCT e.id_number
   2                ,e.pref_name_sort "Sort_Name"
   3                 ,gc.club_description "Club"
   4   FROM entity e
   5       ,tu_gift_totals tgt
   6       ,entity_record_type ert
   7       ,address pref
   8       ,(SELECT g.gift_club_id_number
   9               ,g.gift_club_status
  10               ,gct.club_description
  11           FROM gift_clubs      g
  12               ,gift_club_table gct
  13          WHERE g.gift_club_code = gct.club_code
  14            AND substr(g.gift_club_end_date, 1, 4) = '2007'
  15            AND g.gift_club_status = 'L'
  16            AND g.school_code = 'FL'
  17            AND 0 = (SELECT COUNT(*)
  18                       FROM gift_clubs      zz
  19                           ,gift_club_table gctz
  20                      WHERE zz.gift_club_code = gctz.club_code
  21                        AND zz.gift_club_id_number = 
g.gift_club_id_number
  22                        AND zz.gift_club_status = 'L'
  23                        AND zz.school_code = 'FL'
  24                        AND substr(zz.gift_club_end_date, 1, 4) = '2007'
  25                        AND gctz.club_year_type > gct.club_year_type)
  26         ) gc
  27  WHERE
  28  --Joins
  29  e.id_number = tgt.id_number(+)
  30  AND e.id_number = ert.id_number
  31  AND e.id_number = pref.id_number
  32  AND e.id_number = gc.gift_club_id_number(+)
  33  AND pref.addr_pref_ind = 'Y'
  34  AND pref.addr_status_code = 'A'
  35  AND e.record_status_code = 'A'
  36  --Input Criteria
  37  AND ert.school_code = 'FL'
  38  AND tgt.school(+) = 'FL'
  39  AND ert.class_year = '1968'
  40  --Fletcher Class Year
  41  AND
  42  (   ert.record_type_code = 'AL'
  43   OR
  44      (ert.record_type_code = 'GA' AND
  45      ert.id_number NOT IN (SELECT z.id_number
  46                                FROM entity_record_type z
  47                               WHERE z.record_type_code = 'AL'
  48                                 AND z.school_code = 'FL'))
  49   OR
  50      (ert.record_type_code = 'ST' AND ert.class_year = '2007')
  51  )
  52  -- Part of test to limit to the Small Household
  53  AND e.pref_name_sort like '%SMALL%'
  54  ORDER BY id_number;

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l