Contents Index GETDATE function [Date and time] GRAPHICAL_ULPLAN function [Miscellaneous]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

GRAPHICAL_PLAN function [Miscellaneous]


Function 

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.

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

Value Description
0 Include optimizer estimates only. (default)
1 Include actual summary statistics from execution.
2 Include detailed actual statistics.

cursor-type     A string. Cursor-type can be asensitive (default), insensitive, sensitive, or keyset-driven.

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.
Standards and compatibility 
See also 

PLAN function [Miscellaneous]

EXPLANATION function [Miscellaneous]

GRAPHICAL_ULPLAN function [Miscellaneous]

LONG_ULPLAN function [Miscellaneous]

SHORT_ULPLAN function [Miscellaneous]

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.xml.

SELECT GRAPHICAL_PLAN(
   'SELECT * FROM department WHERE dept_id > 100' );
OUTPUT TO plan.xml FORMAT FIXED

The following statement returns a string containing the graphical plan for a keyset-driven, updatable cursor over the query 'SELECT * FROM department WHERE ....'. 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 department WHERE dept_id > 100',
   2,
   'keyset-driven', 'for update' )

In Interactive SQL, you can view the plan for any SQL statement on the Plan tab in the Results pane.


Contents Index GETDATE function [Date and time] GRAPHICAL_ULPLAN function [Miscellaneous]