I've been following this thread with interest. I can't resist entering my humble opinion.
Den 2014-01-07 10:38 skrev Mark Rotteveel såhär: > As I see it, optimizer hints are a different beast from limiting the > result set using FIRST. The first is a way to influence decisions of the > optimizer, and the second describes what is to be delivered (number of > rows; which might have a default optimizer behavior, which might be > overridden by an optimizer hint(!)). 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. In the long run it means that for a DBA/developer to be able to create good performing queries you will increasingly need specific FB knowledge about FB specific and inherently non standard optimizer hints. This is not good, and I think it should be considered only as a last resort after trying every other viable solution. What's the problem we're actually trying to solve here? Dmitry is asking for a way to let the server know if the user wants to put priority on 1) "quickest possible response time for first N records" as opposed to priority on 2) "quickest possible response time for the entires result set". This is something that, as Dmitry said, cannot be deduced from the actual SQL, unless FIRST or ROWS is used. 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. Case 1a) with known N is already possible using FIRST/ROWS. Case 1a) with unknown N could be supported by allowing some variant of FIRST/ROWS with unspecified N, e.g. "FIRST ANY". Case 1b) is a bit harder. You would like to tell the engine that it's important to be able to quicky START working with the result set, but less important to be able to quickly FINISH working with ALL of it (and also important enough that you're prepared to put extra net load on the server to achieve it, as pointed out by Ann). As far as I can see this is something that is inherently a pure optimizer thing, whichever way you look at it. So to be able to support it, we do need some syntax that specific for this purpose and nothing else. The though passed my mind, that perhaps this case might be uncommon enough that it would be alright to have to resort to specifying the complete plan, but the major drawback of this is that the plan has to be "complete", i.e. you need to write a possibly large and complex plan to achieve a change in only a small part of it. 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 can't see it myself... At the end of the day, if the plan clause cannot be modified as suggested, I do think that this would be a useful feature, but is it useful enough to warrant implementation of special syntax? I have no strong opinion here... But, if it is to be implemented, I see it as something very similar to the already existing plan clause, because that also is FB specific, and only deals with the optimizer as opposed to the actual query semantics. Could it be made a variant of plan? As I see it you either want to give the optimizer a hint, or you will dictate the full plan, so I see no reason to support both hint and plan. In other words, there would be no conflict to allow for both hint and a complete plan (mutually exclusive) inside the plan clause. Would this be possible: plan hint "Movies" order "IX_MovieName" This would tell the optimizer that the query should try to use index retrieval for the "Movies" table, which is what we're after. I like this approach because it avoids implementation of some new keyword/syntax outside the already optimizer-specific plan clause. I also like it because it has some other potential uses. In the support forum I often see suggestions to add 0 or similar to avoid using some index. This could also be achieved using plan hints: plan hint "Movies" natural or plan hint "Movies" index "SomeBetterIndex" to avoid using some less-than-optimal index on the table "Movies". If this is not possible or "voted down", I at least strongly vote for some SQL implementation. Parsing comments is a muck-up. Regards, Kjell -- -------------------------------------- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 ------------------------------------------------------------------------------ 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
