Strange behavior

From: Stephen Andert <StephenAndert_at_firsthealth.com>
Date: Thu, 15 Jan 2004 07:59:35 -0800
Message-ID: <F001.005DCEDE.20040115075935@fatcity.com>

Hello again,

Sorry if this is the 2nd msg in a short time, but Oracle Support amazed me with their quick response this morning and I haven't seen many messages (or any from me) on the list this morning.

I promised to keep you updated on this strangeness. After further testing, we have discovered that the queries returning the wrong values return the correct values when /*+rule */ is added. I don't like telling my developers that until I have a better solution, so we are still working a TAR with the folks at Oracle Support who have come up with this.

UPDATE:

This appears to be related to the bugs referenced above. Bug 2700474 is fixed in the 9.2.0.4 patch set.

ACTION PLAN:

Without the rule hint if you alter the session as listed below are the correct results returned? SQL>alter session set "_complex_view_merging"=false ; Then execute the query without the rule hint.
If this returns the correct results, the parameter can be set at the database level.

3. To obtain a bug fix, the latest patch set (9.2.0.4) can be applied.

"_complex_view_merging"=false did not work in my test, so it looks like we have more motivation to continue working towards 9.2.0.4.

Thanks for your troubleshooting help.
Stephen

->->->->->->->->->->->->->->->

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(*)

--
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: Michael Boligan
  INET: michael.boligan.b_at_bayer.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: 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).

Received on Thu Jan 15 2004 - 09:59:35 CST

Strange behavior - update