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
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: Stephane Faroult [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, October 16, 2002 4:06 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Urgent problem with query in CBO Vs RBO
> "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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
