| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Wrong results from SQL in 8174
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Wednesday, May 05, 2004 5:21 PM
To: oracle-l_at_freelists.org
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu May 06 2004 - 17:06:47 CDT
 |
 |