Re: Unexpected inline view behavior

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Tue, 12 Jun 2012 23:51:56 +0300
Message-ID: <CAMHX9J+ojHLcUS-SMjA20e9TNpH0f4KLtNtqtm33aoTP3aQHZg_at_mail.gmail.com>

It's a bug - where a bugfix for bug 7597159, when combined with other transformations, has caused other bugs to show up :-) Try with the bugfix disabled - you should get a proper error then:

*alter session set "_fix_control"='7597159:off';*

In the optimizer trace (when it wrongly returns a result) you'd probably see this:

...
Count(col) to Count(*) (CNT)

---

CNT:     COUNT() to COUNT(*) not done.
SVM:     SVM bypassed: Single grp set fct (aggr) without group by.

*SLP: Removed select list item BAR from query block SEL$2* ...

SLP = Select List Pruning, but in this case it messes up the result...

--
Tanel Poder
http://blog.tanelpoder.com
Expert Oracle Exadata book:
http://www.apress.com/9781430233923


On Tue, Jun 12, 2012 at 9:03 PM, Tornblad, John <JTornblad_at_emdeon.com>wrote:



> I had a very large query that was going off the rails in terms of

> performance and unexpected results.  After dissection, I zeroed in on a

> flawed inline view, but it was flawed in a way that (a) looked like an

> error but did not raise an error when parsed and (b) worse, yielded an

> incorrect or at least unexpected result and caused havoc.  I am assuming

> there is something I didn't know about what this inline view is doing,

> or how it is being parsed / reorganized (?)... but it's a mystery to me.

>

> Boiling it down...

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 12 2012 - 15:51:56 CDT