On 01/06/2014 08:38 PM, Ann Harrison wrote:
Dmitry,
> Right. And if you want to give the optimizer a hint that it
should choose
> the second plan, change the query like this:
>
> select first 1000000 * from MOVIES where COMMENTS like '%yacht%
order
> by NAME;
>
> If you think your client may want more than a million rows, increase
> that number.
I mentioned this approach in my initial message and it does not look
good to me. I treat it as a workaround / hack, not a solution.
The FIRST keyword was added to Firebird's SQL in version 1.0 or 1.1 -
in 2000 0r 2001, but the use of FIRST in InterBase goes back much further.
It was part of GDML - and it's purpose was to signal that the query should
be optimized to walk the index (navigate, in the internals) rather
than create
a bitmap and access records in storage order. There's no reason to
limit
the number of record a query returns - when you've seen enough, just
close
itt. The semantic significance of FIRST and its relatives is "I want
the first
records quickly."
So, historically, no, it's neither a workaround nor a hack. Parsing SQL
comments ... that sounds like a hack to me. Adding yet another
non-standard keyword when one already exists and does what you want
doesn't appeal all that much either.
So you suggest to use FIRST without number of records? I see almost no
problems with this approach - the only small question is weather btyacc
can handle such grammar well.
Also, there may be (in fact, there are) customers who need the FIRST
ROWS strategy being the default one. And rewriting a majority of their
queries to include the dummy FIRST clause is not something they can
consider seriously.
So would you consider a connection option? A transaction option?
Or even config file option. Not related with SQL IMHO.
------------------------------------------------------------------------------
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
