On 1/2/2014 8:10 AM, Simonov Denis wrote:
> Dmitry Yemanov <firebi...@yandex.ru> писал(а) в своём письме Thu, 02 Jan
> 2014 15:55:57 +0400:
>
>
>> This makes it necessary to allow the FIRST ROWS mode when it's needed.
>> I'm proposing the following:
>>
>> 1) FIRST ROWS mode is implicitly used when the FIRST / ROWS clause is in
>> game. Also, FIRST ROWS mode is implicitly used for EXISTS / ANY
>> subqueries.
>>
>> 2) An explicit clause is introduced to force the non-default mode (ALL
>> ROWS for FIRST-claused queries and FIRST ROWS for other queries). Hating
>> Oracle-like pseudo-comments with embedded hints and preferring explicit
>> syntax, I suggest (inspired by DB2):
>>
>> SELECT ...
>> [OPTIMIZE FOR {FIRST | ALL} ROWS]
>>
> Syntax for tips aka DB2 I like more, but it reduces the portability of SQL
> queries themselves between different DBMS, while syntax Oracle (as a
> comment) does not have this disadvantage. If all the tips are localized in
> one place it may slightly alleviate this problem.
> About the default optimization as ALL ROWS fully agree.
>
>I presume everyone knows about standard SQL "select ... OFFSET <n> ROWS ... FETCH <n> ROWS", et al? I believe that gives the optimizer everything it could want to know. My experience over 30 years is that giving the optimizer hints generally halts improvements to the optimizer. Depending on the optimizer to optimize is the only motivation to improve it. Building in optimizer hints generally means you've thrown in the towel. ------------------------------------------------------------------------------ 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
