6.1.7. The PLAN clause

The PLAN clause enables the user to submit a data retrieval plan, thus overriding the plan that the optimizer would have generated automatically.

Syntax

PLAN <plan-expr>
<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
<sorted-item> ::= SORT (<plan-item>)
<joined-item> ::=
  JOIN (<plan-item>, <plan-item> [, <plan-item> ...])
<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])
<plan-item> ::= <basic-item> | <plan-expr>
<basic-item> ::=
  <relation> { NATURAL
             | INDEX (<indexlist>)
             | ORDER index [INDEX (<indexlist>)] }
<relation> ::= table | view [table]
<indexlist> ::= index [, index ...]

Every time a user submits a query to the Firebird engine, the optimizer computes a data retrieval strategy. Most Firebird clients can make this retrieval plan visible to the user. In Firebird’s own isql utility, this is done with the command SET PLAN ON. If you are studying query plans rather than running queries, SET PLANONLY ON will show the plan without executing the query.

In most situations, you can trust that Firebird will select the optimal query plan for you. However, if you have complicated queries that seem to be underperforming, it may very well be worth your while to examine the plan and see if you can improve on it.

Simple plans

The simplest plans consist of just a relation name followed by a retrieval method. For example, for an unsorted single-table select without a WHERE clause:

select * from students
  plan (students natural);

If there’s a WHERE or a HAVING clause, you can specify the index to be used for finding matches:

select * from students
  where class = '3C'
  plan (students index (ix_stud_class));

The INDEX directive is also used for join conditions (to be discussed a little later). It can contain a list of indexes, separated by commas.

ORDER specifies the index for sorting the set if an ORDER BY or GROUP BY clause is present:

select * from students
  plan (students order pk_students)
  order by id;

ORDER and INDEX can be combined:

select * from students
  where class >= '3'
  plan (students order pk_students index (ix_stud_class))
  order by id;

It is perfectly OK if ORDER and INDEX specify the same index:

select * from students
  where class >= '3'
  plan (students order ix_stud_class index (ix_stud_class))
  order by class;

For sorting sets when there’s no usable index available (or if you want to suppress its use), leave out ORDER and prepend the plan expression with SORT:

select * from students
  plan sort (students natural)
  order by name;

Or when an index is used for the search:

select * from students
  where class >= '3'
  plan sort (students index (ix_stud_class))
  order by name;

Notice that SORT, unlike ORDER, is outside the parentheses. This reflects the fact that the data rows are retrieved unordered and sorted afterwards by the engine.

When selecting from a view, specify the view and the table involved. For instance, if you have a view FRESHMEN that selects just the first-year students:

select * from freshmen
  plan (freshmen students natural);

Or, for instance:

select * from freshmen
  where id > 10
  plan sort (freshmen students index (pk_students))
  order by name desc;

Composite plans

When a join is made, you can specify the index which is to be used for matching. You must also use the JOIN directive on the two streams in the plan:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s natural, c index (pk_classes));

The same join, sorted on an indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan join (s order pk_students, c index (pk_classes))
  order by s.id;

And on a non-indexed column:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  plan sort (join (s natural, c index (pk_classes)))
  order by s.name;

With a search added:

select s.id, s.name, s.class, c.mentor
  from students s
  join classes c on c.name = s.class
  where s.class <= '2'
  plan sort (join (s index (fk_student_class), c index (pk_classes)))
  order by s.name;

As a left outer join:

select s.id, s.name, s.class, c.mentor
  from classes c
  left join students s on c.name = s.class
  where s.class <= '2'
  plan sort (join (c natural, s index (fk_student_class)))
  order by s.name;

If there is no index available to match the join criteria (or if you don’t want to use it), the plan must first sort both streams on their join column(s) and then merge them. This is achieved with the SORT directive (which we’ve already met) and MERGE instead of JOIN:

select * from students s
  join classes c on c.cookie = s.cookie
  plan merge (sort (c natural), sort (s natural));

Adding an ORDER BY clause means the result of the merge must also be sorted:

select * from students s
  join classes c on c.cookie = s.cookie
  plan sort (merge (sort (c natural), sort (s natural)))
  order by c.name, s.id;

Finally, we add a search condition on two indexable colums of table STUDENTS:

select * from students s
  join classes c on c.cookie = s.cookie
  where s.id < 10 and s.class <= '2'
  plan sort (merge (sort (c natural),
                    sort (s index (pk_students, fk_student_class))))
  order by c.name, s.id;

As follows from the formal syntax definition, JOINs and MERGEs in the plan may combine more than two streams. Also, every plan expression may be used as a plan item in an encompassing plan. This means that plans of certain complicated queries may have various nesting levels.

Finally, instead of MERGE you may also write SORT MERGE. As this makes absolutely no difference and may create confusion with “real” SORT directives (the ones that do make a difference), it’s probably best to stick to plain MERGE.

MERGE (unsorted stream, unsorted stream)