Difference between revisions of "EXPLAIN"

From Recital Documentation Wiki
Jump to: navigation, search
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
{{YLM to do}}
 
 
==Class==
 
Performance and Optimization
 
 
 
 
==Purpose==
 
==Purpose==
 
+
Display information about the optimization and result count of an SQL SELECT statement
  
  
 
==Syntax==
 
==Syntax==
 
+
EXPLAIN <SELECT statement>
  
  
 
==See Also==
 
==See Also==
[[SQL SELECT|SELECT]]
+
[[CLOSE ALTERNATE]], [[CREATE INDEX]], [[INDEX]], [[PROFILE]], [[SQL SELECT|SELECT]], [[SET ALTERNATE]], [[SET CONSOLE]], [[SQLCNT]]
  
  
 
==Description==
 
==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 perfomance of SELECT statements.
  
  
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
EXPLAIN SELECT * FROM example
+
> EXPLAIN SELECT * FROM example
 
sqlcnt=100
 
sqlcnt=100
EXPLAIN SELECT * FROM example WHERE start_date < date()
+
> EXPLAIN SELECT * FROM example WHERE start_date < date()
 
Could not optimize WHERE condition
 
Could not optimize WHERE condition
 
sqlcnt=100
 
sqlcnt=100
EXPLAIN SELECT * FROM example WHERE account_no = '000'
+
> EXPLAIN SELECT * FROM example WHERE account_no = '000'
 
Optimized using index tag 'ACCOUNT_NO'
 
Optimized using index tag 'ACCOUNT_NO'
 
sqlcnt=99
 
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
 
</code>
 
</code>
  
 
==Products==
 
==Products==
Recital Database Server, Recital Mirage Server, Recital Terminal Developer
+
Recital Server, Recital  
 
[[Category:Documentation]]
 
[[Category:Documentation]]
 
[[Category:SQL]]
 
[[Category:SQL]]
 
[[Category:Commands]]
 
[[Category:Commands]]

Latest revision as of 17:18, 10 November 2009

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, 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 perfomance of SELECT statements.


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

Products

Recital Server, Recital