RE: Wrong results from SQL in 8174
- From: "Poras, Henry R." <Henry_Poras@xxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 May 2004 17:51:08 -0400
Sounds close, but...
Since "common subquery elimination" depends on two parts of the WHERE clause
separated by an OR operator, I split my SQL into two pieces and eliminated the
OR. I ran both of these SQLs with both ranges (larger and smaller BETWEEN
clause). All four result sets seemed correct (at least large and small range for
SQLa were identical, as were large and small range for SQLb). That seems like
"common subquery elimination".
So I tried the workaround given by Oracle, either setting
"optimizer_features_enable=8.1.6" or "_eliminate_common_subexpr = false". Even
with these set in the init.ora, the bug remained. Guess I'll dig more tomorrow.
Henry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Wednesday, May 05, 2004 5:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Wrong results from SQL in 8174
Could that be the "common subquery elimination" optimization bug?
I believe it was supposed to be fixed in 8.1.7.4.
Try setting optimizer_features_enabled=8.1.6 and see if that makes a
difference.
At 02:20 PM 5/5/2004, you wrote:
>Seems to be the day for SQL bugs. A developer here was running a 3 table join.
>Part of the WHERE clause was a BETWEEN statement. When he increased the
>range of
>the BETWEEN, the number of records returned by the query dropped. I ran a
>sql_trace/tkprof to compare explain plans and row counts. Execution path
>changed
>(increasing the range in the BETWEEN increased the expected # of rows returned
>from the table. Thus the new plan.). The row counts in the problem
>execution-plan made no sense to me. I tried running both SQL's with an ORDERED
>hint to force the same execution plan. Both result sets were now the same.
>Definitely a SQL bug.
>
>So now I'm having developers ask "what assurance do we have that the
>numbers we
>are reporting in any report are correct?" I'm still trying to track down the
>root cause of this bug. Anyone else seen it? SQL and explain plans follows.
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
