Re: Can we expect an enhancement for derived table merge optimization?

[Roy Lyseng]

Hi Rick,

Rick James Wrote:
-------------------------------------------------------
> Roy, thanks again.
>
> > views are pretty well integrated into the
> optimizer code by now
>
> When I can stop saying that VIEWs are poorly
> optimized? That is, what version fixed most of
> the VIEW code? Most people don't run 5.6 (yet).
> On these forums, I estimate that nearly half are
> not up to 5.5 yet, and only a few percent are
> using 5.6. At my work, we are struggling to get
> 5.5 deployed. (That's our problem, not
> Oracle's.)

Well, that is always a struggle. We may target a release that is two years away, and customers start deploying it one year after that...

I can just say: Try it. The view handling in 5.5 is pretty mature. View merging works fine in 5.5, but materialization happens early, you have to install 5.6 for improved materialization support. Derived tables are always materialized in 5.5, of course.


> > and some wrong results with outer joins combined
> with views in a few circumstances
>
> Do you mean incorrect resultset? Or merely
> inefficient optimization?

The result is incorrect data, but the use cases are very rare. It seems to be about using views as inner table of an outer join, a NULL-extended row, and selecting a literal value from that row. This value should be returned as NULL, but it is the actual literal that is returned.

Thanks,
Roy