Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

EXPLAIN PLAN

Description

Generates a query execution plan for a SQL query.

Syntax

EXPLAIN PLAN [ INTO [ owner_name. ] table_name ] [ SET STATEMENT_ID = <stmt id> ] FOR <SQL statement>

Arguments

table name

The table into which the query plan is to be saved. Any valid table name may be specified. If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified he should have adequate privilege to write into it.

Note: The INTO table_name clause is optional. If it is not specified, then the query plan is stored in the default plan table admin.qep_tbl.

stmt_id

The user provided identifier for the query plan. Any character string (less than 32 characters) can be specified. The stmt_id is stored along with the query plan and is used to distinguish between the various Query plans stored in the Query plan table.

The SET STATEMENT_ID = <stmt id> clause is optional. If not specified, a unique ID of the form qep_stmtid_xx is generated internally.

Notes

For complex SQL operation, the query plan of the SQL query being executed has a very large impact on its performance. It is useful to have a mechanism wherein the execution plan of a query can be made available before actually executing the query. This aids in designing efficient queries.

FairCom DB SQL has the ability to dynamically generate and store query plans. The query plan can be generated and even viewed graphically with the FairCom DB SQL Explorer utility.

An internal stored procedure is used to retrieve the query plan.

access_get_qep( table_owner, table_name, stmt_id, max_nodeinfo_len )

  • table_owner is the owner of the query plan table
  • table_name is the name of the query plan table where the plan is stored
  • stmt_id is the Statement Id provided by the user or the id internally generated when the execution plan was generated.
  • max_nodeinfo_len (integer) - is the length of the information displayed per node in the resultset. By default this is set to 256. Set this value to 0 if the default value is to be used (8192).

The output of the procedure is a resultset consisting of a single column. Each record of the resultset corresponds to a node in the query plan. The ordering of the nodes in the resultset is, from bottom to top and left to right of the query execution plan tree.

Authorization

If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified they should have adequate privilege to write into it.

SQL Compliance

Extension

Environment

Embedded SQL, interactive SQL, ODBC applications

TOCIndex