On Tue, Sep 14, 2010 at 6:15 PM, Dave Crooke <dcrooke@gmail.com> wrote:
> I presume there is more usage of this view than just those 3 queries
> (otherwise, for a start there would be no need for d, e, f in the view
> definition)
>
> Why not just rewrite these 3 queries to go directly off the main table? Or,
> create a different view without the sort_by in its definition?
>
> Or, if these are used very frequently and performance is critical, consider
> (i) caching these results in the application layer, with logic to understand
> when they need to be updated, or (b) maintaining extra tables that just
> contain (a) (a,b) and (a,b,c)
>
> Objectively, it's always better to optimize the SQL and application level
> for the specific needs of the situation before concluding that the
> underlying database engine should do these optimizations automatically, and
> it seems like there are a number of options you could explore here.
Question here is not how to do it right, but how to make the optimizer smarter
than it is now, taking rid of work not needed.
Regards
Gaetano Mendola
--
cpp-today.blogspot.com