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:
============
1. 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.
2. If this returns the correct results, the parameter can be set at the
database level.
or
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(*)
------------
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: Michael Boligan
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: 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).
