Subject Re: [IBDI] Firebird 1
Author Jason Wharton
> Do you have a problem with exposing the FIRST n functionality to possibly
> limit the size of the data packets returned from the server?

Yes and no. I think it would be useful for the server to know if the person
fetching the data is interested primarily in the entire dataset or just the
first records that will accurately come to the client. In some cases there
won't be any difference if the whole query has to be processed before it
knows which records will be the first n records but there are cases where
one way is better than another.

If the entire query is going to be processed then it is more efficient to
scan data in natural order. To scan data by scanning an index has you
rattling from data page to data page and really mucking things up. But if
you are only interested in the first few rows and want them returned quickly
a little bit of rattling is much better than waiting for a full natural

I would like some way to indicate to the optimizer what the intentions of
the query are.

Fast first or fast full...

It could be a little bit of an abuse to the SQL dialect but I think we could
hijack the ALL token to indicate that fast full should be used and have it
default to fast first.

SELECT ALL <field list> FROM <tables> .... for fast full
SELECT <field list> FROM <tables> .... for fast first

I would be willing to bet Jim would take issue with these preferences so
perhaps we could leave defaults as-is and introduce a token somewhere else.
The PLAN clause is proprietary to begin with AFAIK so some abuse there
wouldn't be so bad.

PLAN FIRST <original plan>
PLAN FULL <original plan>

But, if we really wanted to get bold, alter the SELECT syntax to allow this:

SELECT FIRST <field list> FROM <tables> .... for fast first preference

I would be pleased about it.

Jason Wharton
CPS - Mesa AZ