I've seen similar behavior in Oracle7 with a logically corrupted index. Could you do an explain plan on each and see what the access paths are for each?
Daniel Fink Stephen Andert wrote: > Greetings fellow-DBA-folk: > > When I run several queries, I am getting very strange results. > Selecting * from apple returns the expected data records, but count(*) > does not. > Selecting anything other than * from apple says no records. > The subquery for pear works fine on it's own. > Selecting anything other than * from apple returns the expected records > when about 900+ values are placed literally in the subquery as shown in > example 4 below. > > Am I missing something? Anyone have a direction for me to look for the > cause of this? > > Thanks > Stephen > > EX 1 > select rowid from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > no rows selected > > EX 2 > select rownum from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > no rows selected > > EX 3 > select * from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > --- 9 rows returned, I removed them for confidentiality reasons --- > > EX 4 > select count(*) from common.apple > where appleinter1 in ( 7009 , 7010 , 7011 , 7012 , 7013 , 7014 , 7015 , > 7016 , 7017 , 7018 , 7019) > and appletermd is null > and rownum < 10 ; > > --- 9 rows returned, I removed them for confidentiality reasons --- > > EX 5 > select count(*) from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null; > > COUNT(*) > ------------ > 0 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Andert > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
