| 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: Urgent problem with query in CBO Vs RBO
Raj,
We'd love to see the query in question. And, if feasible an example of the correct and incorrect results. Can you provide this?
Cheers,
Mark.
"Jamadagni,
Rajendra" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<Rajendra.Jamadagni cc:
@espn.com> Subject: RE: Urgent problem with query in CBO Vs RBO
Sent by:
root_at_fatcity.com
17/10/02 06:20
Please respond to
ORACLE-L
Thanks ...
so far Oracle had completely understood the problem and looks like we may be hitting some bug in CBO. I am awaiting the bug#, I don't know that yet.
The same query works fine if I do a 'alter session set optimizer_mode=rule'; though ..
Raj
-----Original Message-----
Sent: Wednesday, October 16, 2002 4:06 PM
To: Multiple recipients of list ORACLE-L
> "Jamadagni, Rajendra" wrote:
>
> Okay ... so I have a query that works fine in RBO (8161). In CBO
> (9201) is returns wrong results (stats are recreated this AM). By
> adding a group by the CBO query returns the right data.
>
> Furthermore on CBO, the explain plan shows that Oracle is not even
> parsing the query completely. If drops off a huge EXISTS clause.
>
> Does anyone know what is going on? I have a sev 1 tar open and waiting
> for OWS to call me.
>
> Raj
Raj,
I understand your concern about the disappearing EXISTS but I think
that it would be sensible to try to get the plan using different tools
(plain old EXPLAIN followed by a query on PLAN_TABLE, set autotrace,
tkprof ...) to be sure that the bug really is in the parser, and not in
the output of the plan.
Your 'adding a GROUP BY' leaves me a little suspicious. Are you sure
that your query was correct (in the absolute sense) initially? I am
asking the question because I remember one day somebody coming to me
triumphantly saying 'I have found a big bug in Oracle' because a query
which used to give the good result (not exactly the same as 'which was
correct') was suddenly (after I think the addition of hints) miserably
failing with a mysterious data conversion error. On closer inquiry, it
appeared that a VARCHAR2 column was compared to a number, which, as most
people who once asked themselves 'Why is Oracle *NOT* using my index ?'
know, forces an implicit to_number() of the VARCHAR2 column. What
happened is that this column mostly contained numbers represented as
strings, but for a '***' or similar somewhere. With one execution plan,
other criteria caused this row to be filtered out before the comparison
on this very column. Not with the other execution plan.
The GROUP BY sounds a bit like a DISTINCT to me, a convenient way to fix
a missing join, or perhaps some hazardous play on ROWNUM or similar.
Just my gut feeling.
HTH,
Stephane Faroult
Oriole Software
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: sfaroult_at_oriole.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). (See attached file: ESPN_Disclaimer.txt) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Received on Wed Oct 16 2002 - 19:08:56 CDT-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au 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).
- application/octet-stream attachment: ESPN_Disclaimer.txt
 |
 |