Marvin wrote:

> I'm wondering if anyone out there knows the inside workings of the
> select FIRST(x) mechanism.

Yes.

> My question is, In a complex select First query with inner joined
> tables, does firebird do the required calculation and comparing to
> attain the first X num of records, then stop executing and return the
> results OR does the required calculation and comparing for ALL records
> but only returns the first X??

That depends.

If you don't include an order by, then the first x records are returned
and the query stops. That's OK, more or less, but it does return an
arbitrary set of records which is not generally useful.

If you do include an order by, then the method of execution depends on
the existence of an index that supports the ordering, also called a
navigational index. And that in turn depends not only on the existence
of an index that matches the order by fields, but also on the plan
chosen for the query. And here it gets complicated.

The simple answer is that Firebird will generally use a navigational
index only on single table queries. V2.0 may change this - I don't
know.

What's a navigational index.

Firebird and InterBase differ from most databases in the strategy used
for retrieving records by index. Most databases go to the index, find
the next qualifying row, go to the data, read that row, and return to
the index to find the next qualifying row. That works well for
clustered indexes - where the data is stored either in the index or
ordered by the index field. It's lousy for secondary indexes. That
style of index usage is, in Firebird terms, navigational.

InterBase and Firebird normally read the index, making a list of rows
that qualify. The list is ordered by page, so when a page is read,
all rows on that page that qualify are read at once. If you have more
than one indexable term on a table, Firebird can use both indexes.
For example, if you want to find sales between $50,000 and $75,000 in
Wilmington North Dakota in the first week of February, 1995 and you
have indexes on amount, date, and location, Firebird will create a
bitmap of records that qualify by date, a bitmap of records that qualify
by location, and a bit map of records that qualify by amount. It then
ands the bitmaps together so it retrieves only those rows that qualify
on all three. Hard to do navigationally.

Regards,


Ann