EXPLANATION function [Miscellaneous]

Returns the optimization strategy of a SQL statement as a plain text string.

Syntax

EXPLANATION( 
string-expression  
[ , cursor-type ]
[, update-status ]  
)

Parameters

string-expression The SQL statement, which is commonly a SELECT statement, but can also be an UPDATE, MERGE, or DELETE statement.
cursor-type A cursor type, expressed as a string. Possible values are asensitive, insensitive, sensitive, or keyset-driven. If cursor-type is not specified, asensitive is used by default.

update-status A string parameter accepting one of the following values indicating how the optimizer should treat the given cursor:

Value	Description
READ-ONLY	The cursor is read-only.
READ-WRITE (default)	The cursor can be read or written to.
FOR UPDATE	The cursor can be read or written to. This is the same as READ-WRITE.

Returns

Remarks

The statement's access plan is returned as a string. For information about interpreting the result, see Advanced: Query execution plans.

The GRAPHICAL_PLAN function offers significantly greater information about access plans, including system properties that may have affected how the statement was optimized.

This information can help you decide which indexes to add or how to structure your database for better performance.

Standards and compatibility

Example

The following statement passes a SELECT statement as a string parameter and returns the plan for executing the query.

SELECT EXPLANATION( 'SELECT * FROM Departments WHERE DepartmentID > 100' );

The following statement returns a string containing the short form of the text plan for an INSENSITIVE cursor over the query 'select * from Departments where ....'.

SELECT EXPLANATION( 'SELECT * FROM Departments WHERE DepartmentID > 100',
   'insensitive', 'read-only' );

Discuss this page in DocCommentXchange.