The

CommentsProcedural SQL (PSQL) Statements

The `WINDOW` Clause

The WINDOW clause defines one or more named windows that can be referenced by window functions in the current query specification.

Syntax

<query_spec> ::=
  SELECT
    [<limit_clause>]
    [<distinct_clause>]
    <select_list>
    <from_clause>
    [<where_clause>]
    [<group_clause>]
    [<having_clause>]
    [<named_windows_clause>]
    [<plan_clause>]

<named_windows_clause> ::=
  WINDOW <window_definition> [, <window_definition> ...]

<window definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  !! See Window (Analytical) Functions !!

In a query with multiple SELECT and WINDOW clauses (for example, with subqueries), the scope of the `new_window_name_ is confined to its query context.That means a window name from an inner context cannot be used in an outer context, nor vice versa.However, the same window name can be used independently in different contexts, though to avoid confusion it might be better to avoid this.

For more information, see [fblangref50-windowfuncs].

Example Using Named Windows

select
  id,
  department,
  salary,
  count(*) over w1,
  first_value(salary) over w2,
  last_value(salary) over w2
from employee
window w1 as (partition by department),
       w2 as (w1 order by salary)
order by department, salary;

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-expression>

<plan-expression> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-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> ...])

<hash-item> ::=
  HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expression>

<basic-item> ::=
  <relation> { NATURAL
             | INDEX (<indexlist>)
             | ORDER index [INDEX (<indexlist>)] }

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]

Table 1. Arguments for the `PLAN` Clause
Argument	Description
table	Table name or its alias
view	View name
index	Index name

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 only interested in looking at query plans, SET PLANONLY ON will show the plan without executing the query.Use SET PLANONLY OFF to execute the query and show the plan.

Note

A more detailed plan can be obtained when you enable an advanced plan.In isql this can be done with SET EXPLAIN ON.The advanced plan displays more detailed information about the access methods used by the optimizer, however it cannot be included in the PLAN clause of a statement.The description of the advanced plan is beyond the scope of this Language Reference.

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 be worth your while to examine the plan and see if you can improve on it.

The

The WINDOW Clause

Example Using Named Windows

The PLAN Clause

The `WINDOW` Clause

The `PLAN` Clause