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@xxxxxxxxxxxxxxx www.proquest.com www.csa.com ProQuest...Start here. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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; -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l