Re: Strange behavior

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: StephenAndert_at_firsthealth.com
>
> 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: ListGuru_at_fatcity.com (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: Daniel.Fink_at_Sun.COM

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: ListGuru_at_fatcity.com (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).