>>I'd suggest to let the legacy PLAN resting in piece. It's already misses many
>>optimizer features and will outdate more and more with years.
Hi, Dmitry
Why plan can not by extended for hint purposes? This is natural place for FB
users to tell optimizer the better way of query execution. This can be created
as visally extended but really different keyword like PLAN HINT ..., PLAN HINT
..., PLAN here the old featured plan ..
Regards,
Karol Bieniaszewski
----- Reply message -----
Od: "Dmitry Yemanov" <firebi...@yandex.ru>
Do: "For discussion among Firebird Developers"
<firebird-devel@lists.sourceforge.net>
Temat: [Firebird-devel] Some aspects of the optimizer hints
Data: wt., sty 7, 2014 16:07
Kjell,
> This makes sense, but I also think what Jim Starkey said makes sense,
> i.e. that introducing optimizer hints in essence means you've given up
> on creating a good automatic optimizer.
Jim is not alone in thinking this way. This is why I explicitly
mentioned that I don't want to discuss either necessity or
implementation of "full featured" hints in this discussion.
> So, when would you need one or the other? As far as I can see, 2) would
> be "the normal case", and 1) would be used only in the the following two
> cases:
>
> 1a) You only want the forst N records, possibly not being able to
> determine N beforehand. You would normally use FIRST N or ROWS N
> (preferred nowadays, isn't it?), but if N isn't known beforehand, this
> is not possible.
>
> 1b) You want to display/start using "something" as quickly as possible
> and then keep fetching more records (possibly the entires result set) in
> the background.
A more common approach might be to fetch the remaining rows by user's
demand. For example, many Delphi applications using DBGrid and its
friends initially fetch only rows fitting just a couple of screens and
continue fetching once user scrolls the grid down.
> Would it be possible to allow specification of only the part of the plan
> that you want to modify and leave the rest "default"?
I'd suggest to let the legacy PLAN resting in piece. It's already misses
many optimizer features and will outdate more and more with years.
> Would this be possible:
>
> plan hint "Movies" order "IX_MovieName"
> plan hint "Movies" natural
> plan hint "Movies" index "SomeBetterIndex"
This could be suitable for generic hints, but not for the FIRST ROWS vs
ALL ROWS choice, as it affects many optimizer decisions at once.
Something like PLAN FOR {ALL | FIRST} ROWS might be a yet another
syntactical option here, although personally I'd prefer to separate
"planning" from "hinting" at the syntax level. So far it was expected
that input (explicitly specified in syntax) and output (reported by the
engine) plans mean the same, but it won't be the case anymore with the
aforementioned approach.
Dmitry
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
