Bruce,
Here's the quote from MetaLink:
"this is bug 1651014 and is fixed in 9i. the workaround is to set
optimizer_features_enabled=8.1.6."
They were less than forthcoming on any other known bugs in 8.1.7...
probably not to anyone's surprise.
larry
Reardon, Bruce (CALBBAY) wrote:
> Larry,
>
> Thanks for the information.
> Do you have a bug number reference for the other problem you mention?
>
> Thanks,
> Bruce
>
> -----Original Message-----
> Sent: Friday, 6 April 2001 14:45
>
> I would strongly suggest the:
>
> optimizer_features_enable = 8.1.6
>
> because it also cures another problem with 8.1.7 wherein a complex query
> with multiple OR and AND conditions will return the wrong number of rows if
> you are using CBO (and/or any statistics are on the resultant table).
>
> larry
>
> At 07:55 PM 4/5/2001 -0800, you wrote:
>
>> Hi,
>>
>> There is a note on Metalink (137430.1 reproduced below) describing a
>
> generic
>
>> 817 optimiser problem that will result in incorrect results when using
>> certain query types.
>>
>> We are currently running 81511 on NT but testing an upgrade to 81711 on NT
>> at the moment.
>>
>> The note suggests 2 workarounds, and my question to others is
>>
>> Should we set "optimizer_features_enable = 8.1.6"
>> -or-
>> should we set "_eliminate_common_subexpr = false"
>>
>> Which method does the list suggest, and what are the implications of each?
>>
>> Thanks,
>> Bruce Reardon
>> mailto:bruce.reardon_at_comalco.riotinto.com.au
>>
>>
>>
>> Doc ID: Note:137430.1
>> results
>> Type: ALERT
>> Status: PUBLISHED
>> Content Type: TEXT/PLAIN
>> Creation Date: 23-MAR-2001
>> Last Revision Date: 28-MAR-2001
>>
>>
>> Common Subquery Elimination in 8.1.7 causes incorrect results
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Versions Affected
>> ~~~~~~~~~~~~~~~~~
>> Oracle Server releases 8.1.7.0 and 8.1.7.1
>>
>> Fixed in 8.1.7.2 and 9.0.0.0
>>
>> Platforms Affected
>> ~~~~~~~~~~~~~~~~~~
>> GENERIC
>>
>> Description
>> ~~~~~~~~~~~
>> The bug is that rows may be returned when none are expected with the
>
> query
>
>> below.
>> Other queries may return more or less rows than expected depending on
>
> the
>
>> predicates used.
>>
>>
>> select * from tab1
>> where (tab1.col1 = 1 and exists (select * from tab2))
>> or (tab1.col1 = 2 and exists (select * from tab3))"
>>
>>
>> There are several variations on this.
>> Eg:
>> select /*+ ALL_ROWS */ * from tab1
>> where (tab1.col1 = 1 and col1 in (select col1 from tab2))
>> or (tab1.col1 = 2 and col1 in (select col1 from tab3))
>> ;
>>
>>
>>
>>
>> Note: The use of the two parts of the WHERE clause separated by an OR
>> operator,
>> each clause containing a non-correlated subquery.
>>
>>
>> The problem is that common subquery elimination treats un-correlated
>> subqueries
>> as identical even when they are not.
>>
>>
>>
>> Likelihood of Occurrence
>> ~~~~~~~~~~~~~~~~~~~~~~~~
>>
>> Any time the above construct is used.
>> This construct is common in queries used by some third party
>
> applications
>
>> including
>> People Soft.
>>
>> This problem only occurs on database versions 8.1.7.0 and 8.1.7.1.
>> Earlier versions are not affected.
>>
>>
>> Possible Symptoms
>> ~~~~~~~~~~~~~~~~~
>> No error is raised.
>> Unless the user is aware of incorrect results the problem will be
>> undetected.
>>
>>
>>
>>
>> Workaround
>> ~~~~~~~~~~
>> As the problem is caused by a new optimizer feature introduced in 8.1.7,
>> the workaround is to set a parameter in the init<SID>.ora to disable the
>> feature.
>>
>>
>> Either set:
>>
>>
>> "optimizer_features_enable = 8.1.6"
>> -or-
>> "_eliminate_common_subexpr = false"
>>
>>
>> Patches
>> ~~~~~~~
>> This is expected to be fixed in the 8.1.7.2 Patch Set.
>> The problem is fixed in Oracle9i.
>> No single one off patches are currently available.
>>
>>
>>
>>
>> References
>> ~~~~~~~~~~
>> QUERY RETURNING ROWS IN 8.1.7 WHEN NO ROWS EXPECTED [BUG:1578644]
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Strickland
INET: stricklandl_at_voyager.spjc.cc.fl.us
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Fri Apr 06 2001 - 06:17:50 CDT
