FirebirdSQL logo

SET PLAN

SET PLAN [ON | OFF]

This command determines whether isql displays the plan used to access the data for each statement executed.By default, ISQL does not display the plan.As with many other commands, not providing a parameter toggles the current state.

SQL> set plan on;

SQL> select emp_no, first_name, last_name
CON> from employee
CON> where emp_no = 107;

PLAN (EMPLOYEE INDEX (RDB$PRIMARY7))

 EMP_NO FIRST_NAME      LAST_NAME
======= =============== ====================
    107 Kevin           Cook

SQL> update employee
CON> set first_name = 'Norman'
CON> where last_name = 'Cook';

PLAN (EMPLOYEE INDEX (NAMEX))

SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

       COUNT
============
          42

The execution plan is displayed before the output of a select statement.

Effects of Various Plan-Related Commands

Usage options

SET PLAN

simple plan + query execution

SET PLANONLY

simple plan, no query execution

SET PLAN + SET EXPLAIN

explained plan + query execution

SET PLAN + SET EXPLAIN + SET PLANONLY

explained plan, no query execution

SET EXPLAIN

explained plan + query execution

SET EXPLAIN + SET PLANONLY

explained plan, no query execution

SET PLANONLY

SET PLANONLY [ON | OFF]

This command prevents isql from actually executing the SQL statement, and instead only shows the plan that would be used to access the data.This command relies on the [isql-set-plan] command.If set plan off had been executed, this command would have no effect, so turning planonly on has the additional effect of executing set plan on implicitly.Executing set planonly off does not implicitly execute set plan off.

SQL> set planonly on;

SQL> select count(*) from employee;

PLAN (EMPLOYEE NATURAL)

As before, not supplying a parameter toggles the current setting.

SET SQLDA_DISPLAY

Added in

Firebird 2.0

SET SQLDA_DISPLAY [ON | OFF]

This is a hidden command which is not mentioned in the output from the help set command.It displays internal details about the SQL statements being executed by isql.

SQL> set sqlda_display on;

SQL> select count(*) from employee;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 580 INT64 scale: 0 subtype: 0 len: 8
  :  name: COUNT  alias: COUNT
  : table:   owner:

                COUNT
=====================
                   42