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: 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).