GRAPHICAL_PLAN function [Miscellaneous]

Returns the plan optimization strategy of a SQL statement in XML format, as a string.

Syntax
GRAPHICAL_PLAN( 
string-expression 
[, statistics-level 
[, cursor-type 
[,  update-status ] ] ] )
Parameters
  • string-expression   The SQL statement, which is commonly a SELECT statement but which may also be an UPDATE or DELETE statement.

  • statistics-level   An integer. Statistics-level can be one of the following values:

Value Description
0 Optimizer estimates only (default).
2 Detailed statistics including node statistics.
3 Detailed statistics.

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 exactly the same as READ-WRITE.
See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Examples

The following Interactive SQL example passes a SELECT statement as a string parameter and returns the plan for executing the query. It saves the plan in the file plan.saplan which can be opened and read using Interactive SQL.

SELECT GRAPHICAL_PLAN(  'SELECT * FROM Departments WHERE DepartmentID > 100' );
OUTPUT TO 'plan.saplan' FORMAT TEXT QUOTE '' HEXADECIMAL ASIS;

The following statement returns a string containing the graphical plan for a keyset-driven, updatable cursor over the query SELECT * FROM Departments WHERE DepartmentID > 100. It also causes the server to annotate the plan with actual execution statistics, in addition to the estimated statistics that were used by the optimizer.

SELECT GRAPHICAL_PLAN(
   'SELECT * FROM Departments WHERE DepartmentID > 100',
   2,
   'keyset-driven', 'for update' );