| 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
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
QOTD: Any clod can have facts, but having an opinion is an art!
-----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).Received on Wed Oct 16 2002 - 15:20:16 CDT-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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).
- text/plain attachment: ESPN_Disclaimer.txt
 |
 |