Difference between revisions of "EXPLAIN"

Revision as of 06:00, 14 June 2016

Purpose

Display information about the optimization and result count of an SQL SELECT statement

Syntax

EXPLAIN <SELECT statement>

See Also

CLOSE ALTERNATE, CREATE INDEX, INDEX, PROFILE, SELECT, SET ALTERNATE, SET CONSOLE, SET EXPLAIN, SQLCNT

Description

When an SQL SELECT statement is preceded by the EXPLAIN command, information is displayed about the optimization that would be carried out on that SELECT statement and the number of results that it would return. Analysis of the EXPLAIN output can be used by the Developer to target index creation for the maximum performance of SELECT statements.

Note: from Lianja v2.1, issuing SET EXPLAIN ON causes all executed SQL SELECT commands to output their EXPLAIN execution plan to the console.

Example

> EXPLAIN SELECT * FROM example
sqlcnt=100
> EXPLAIN SELECT * FROM example WHERE start_date < date()
Could not optimize WHERE condition
sqlcnt=100
> EXPLAIN SELECT * FROM example WHERE account_no = '000'
Optimized using index tag 'ACCOUNT_NO'
sqlcnt=99
 
// Send EXPLAIN output to a text file
set alternate to explain
set console off
set alternate on
EXPLAIN SELECT * FROM example
close alternate