Subject Surprised by (the lack of) plan...
Author Svein Erling Tysvaer
I wrote a fairly complex select statement (mainly the 270 lines with
JOIN clauses were complex) trying to flatten a relational structure (for
further processing in a statistical package that think that if the world
isn't flat, it should be made flat). This select (inner) joins three
tables, and then left (outer) joins a further two tables, one of these a
simple left join containing a NOT EXISTS clause (repeated 14 times) and
the other a LEFT JOIN(JOIN ON) of the type Arno wrote about yesterday
(thanks Arno, I've never used nor needed that before) that also has a
NOT EXISTS that involves two tables. This is repeated 7 times.

With 31 aliased tables in the main select + 30 further references in NOT
EXISTS clauses (or something like that), I wanted to check the plan
before execution to avoid things taking days to finish. However, the
plan that appeared in IB_SQL and Database Workbench wasn't complete -
some tables (more specifically, those that were part of my main select,
24 'subplans' seemed to be present) simply weren't in the plan at all!
Removing the repeated tables but for the first two occurences, made the
main select reappear in the plan.

As I write, I'm executing the real query (with all tables) and have no
reason to believe there is anything wrong with the result or the way
Firebird executes the query. But I'm surprised that the reported plan
was incomplete. I just checked through isql, and that tool didn't report
the full plan either.

I'm using Firebird 1.5.2.