![String search functions for large object columns [CR 415543]](arrow-left.gif){kind=icon}
In Sybase IQ 12.7 ESD #3, authorized users can display query plans in the Java-based Interactive SQL (dbisql) plan window. You can also save and print query plans from dbisql instead of accessing the .iqmsg file or query plan files on the server.
Sybase IQ supports SQL functions GRAPHICAL_PLAN and HTML_PLAN, which return IQ query plans in XML and HTML format, respectively, as a string result set. The behavior of these functions is controlled by database options QUERY_PLAN_TEXT_ACCESS and QUERY_PLAN_TEXT_CACHING.
Users can view query plans from the dbisql plan window in the following ways:
Execute the query and open the plan window. Depending on the plan type you selected from the Plan option (Tools > Options > Plan), the appropriate plan displays in the plan window.
The IQ query plan displays only if the GRAPHICAL_PLAN option is selected. Other plans return the error message, “Plan type is not supported.”
Enter the query in the SQL statements window and select from the menu SQL > Get Plan. Depending on the plan type you selected from the Plan option (Tools > Options > Plan), the appropriate plan displays in the plan window.
The IQ query plan displays only if the GRAPHICAL_PLAN option is selected. Other plans return the error message, “Plan type is not supported.”
Use the SQL functions, GRAPHICAL_PLAN and HTML_PLAN, to return the query plan as a string result.
To access query plans, use the SQL functions, GRAPHICAL_PLAN and HTML_PLAN, for the following queries: SELECT, UPDATE, DELETE, INSERT SELECT, and SELECT INTO.
To save the query plans from Interactive SQL, use GRAPHICAL_PLAN or HTML_PLAN to retrieve the query plan and save the output to a file using the OUTPUT statement.
See “GRAPHICAL_PLAN function [String]” and “HTML_PLAN function [String]” for details.
To view saved plans, select File > Open from the Interactive SQL client menu and navigate to the directory where you saved your plan. You can also print plans displayed on the plan window by selecting File > Print.
Returns the graphical query plan to Interactive SQL in XML format string.
GRAPHICAL_PLAN ( string-expression )
string-expression SQL statement for which the plan is to be generated. It is generally a SELECT statement, but it can be an UPDATE or DELETE, INSERT SELECT, and SELECT INTO statement.
If the user does not provide an argument to the GRAPHICAL_PLAN function, the query plan is returned to the user from the cache. If there is no query plan in the cache, then the message “Plan not available” is returned.
The behavior of GRAPHICAL_PLAN function is controlled by database options QUERY_PLAN_TEXT_ACCESS and QUERY_PLAN_TEXT_CACHING. If QUERY_PLAN_TEXT_ACCESS is OFF (the default), the following message displays in the error dialog box:
Plan not available. The database option QUERY_PLAN_TEXT_ACCESS is OFF
If a user needs access to the plan, the DBA must set option QUERY_PLAN_TEXT_ACCESS ON for that user.
If QUERY_PLAN_TEXT_ACCESS is ON, and the query plan for the string expression is available in the cache maintained on the server, the query plan is returned to the user from the cache.
If the query plan is not available in the cache and the user is authorized to view plans on the client, then a query plan with optimizer estimates (query plan with NOEXEC option ON) is generated and displays on the dbisql client plan window.
Sybase IQ does not support NOEXEC plan
generation for SELECT, UPDATE, DELETE, INSERT
SELECT, and SELECT INTO queries.
When a user requests a query plan that has not yet been executed, the query plan will not be available in the cache. Instead, a query plan with optimizer estimates is returned without QUERY_PLAN_AFTER_RUN statistics.
Query plans for stored procedures are not accessible using the GRAPHICAL_PLAN function. The error message, “The plan for this statement cannot be retrieved” is returned.
Users can view the query plan for cursors opened for IQ queries. A cursor is declared and opened using DECLARE CURSOR and OPEN CURSOR commands. Use the following query to obtain the query plan for the most recently opened cursor:
SELECT GRAPHICAL_PLAN ( );
With the QUERY_PLAN_AFTER_RUN option OFF, the plan can be displayed after OPEN CURSOR or CLOSE CURSOR. However, if QUERY_PLAN_AFTER_RUN is ON, then CLOSE CURSOR must be executed before the user requests the plan.
When Interactive SQL users select the option Short plan, Long plan, or Show UltraLite Plan from the Plan tab under Tools > Options, the following message displays on the plan window.
Plan not available. Plan type is not supported for Sybase IQ queries.
The user must change the plan option to Graphical plan before requesting the plan for an IQ query. UltraLite plans are not supported.
When Interactive SQL users request plans for UPDATE, DELETE, SELECT INTO, and INSERT SELECT queries, the NOEXEC plan is not supported. Set AUTO_REFETCH option OFF to view the plan for the last executed UPDATE, DELETE, SELECT INTO, and INSERT SELECT queries. To access the query plan, explicitly execute the query first and then request the plan.
The following 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 gplan.xml.
If you use the OUTPUT statement’s
HEXADECIMAL clause set to ASIS to get formatted plan output, the
values of characters are written without any escaping — even
if the value contains control characters. ASIS is useful for text
that contains formatting characters such as tabs or carriage returns.
SELECT GRAPHICAL_PLAN ('SELECT * FROM t1');
OUTPUT to 'c:\gplan.xml' hexadecimal ASIS quote '';
The following SQL example returns the query plan from the cache, if available.
SELECT GRAPHICAL_PLAN ( );
“QUERY_PLAN_TEXT_ACCESS option”
“QUERY_PLAN_TEXT_CACHING option”
“AUTO_REFETCH option [DBISQL],” “NOEXEC option,” “QUERY_PLAN_AFTER_RUN option,” and “OUTPUT statement” in the Sybase IQ Reference Manual
PLAN function [Miscellaneous], EXPLANATION function, [Miscellaneous], GRAPHICAL_ULPLAN function [Miscellaneous], LONG_ULPLAN function [Miscellaneous], and SHORT_ULPLAN function [Miscellaneous] in Adaptive Server Anywhere SQL Reference Manual.
Returns query plans in HTML format string.
HTML_PLAN ( string-expression )
string-expression SQL statement for which the plan is to be generated. It is primarily a SELECT statement but can be an UPDATE or DELETE statement.
If the user does not provide an argument to the HTML_PLAN function, the query plan is returned to the user from the cache. If there is no query plan in the cache, then message “No plan available” is returned.
The behavior of the HTML_PLAN function is controlled by database options QUERY_PLAN_TEXT_ACCESS and QUERY_PLAN_TEXT_CACHING. If QUERY_PLAN_TEXT_ACCESS is OFF (the default), the following message displays in the error dialog box:
Plan not available. The database option QUERY_PLAN_TEXT_ACCESS is OFF
If QUERY_PLAN_TEXT_ACCESS is ON, and the query plan for the string expression is available in the cache maintained on the server, the query plan is returned to the user from the cache.
The HTML_PLAN function can be used to return query plans to Interactive SQL using SELECT, UPDATE, DELETE, INSERT SELECT, and SELECT INTO.
Users can view the query plan for cursors opened for IQ queries. A cursor can be declared and opened using DECLARE CURSOR and OPEN CURSOR commands. Use the following query to obtain the query plan for the most recently opened cursor:
SELECT HTML_PLAN ( );
With QUERY_PLAN_AFTER_RUN option OFF, the plan can be displayed after OPEN CURSOR or CLOSE CURSOR. However, if QUERY_PLAN_AFTER_RUN is ON, then CLOSE CURSOR must be executed before the user requests the plan.
When the Interactive SQL user selects the option Short plan, Long plan, or Show Ultralite Plan from the Plan tab under Tools > Options, the following message displays on the plan window.
Plan not available. Plan type is not supported for Sybase IQ queries.
The user must change the plan option to Graphical plan before requesting the plan for an IQ query. UltraLite plans are not supported.
When Interactive SQL users request plans for UPDATE, DELETE, SELECT INTO, and INSERT SELECT queries, the NOEXEC plan is not supported. Set AUTO_REFETCH option OFF to view the plan for the last executed UPDATE, DELETE, SELECT INTO, and INSERT SELECT queries. To access the query plan, explicitly execute the query first and then request the plan.
When the user requests an HTML_PLAN for a SQL Anywhere query or for an OMNI/CIS decomposed query, the following message is returned:
No plan. HTML_PLAN function is not supported for this type of statement or database.
The following SQL example passes a SELECT statement as a string parameter and returns the HTML plan for executing the query. It saves the plan in the file hplan.html.
SELECT HTML_PLAN ('SELECT * FROM t1');OUTPUT to 'c:\hplan.html';
The following SQL example returns the HTML query plan from the cache, if available.
SELECT HTML_PLAN ( );
“GRAPHICAL_PLAN function [String]”
“QUERY_PLAN_TEXT_ACCESS option”
“QUERY_PLAN_TEXT_CACHING option”
“AUTO_REFECT option [DBISQL],” “NOEXEC option,” “QUERY_PLAN_AFTER_RUN option,” and “OUTPUT statement” in the Sybase IQ Reference Manual
PLAN function [Miscellaneous], EXPLANATION function, [Miscellaneous], GRAPHICAL_ULPLAN function [Miscellaneous], LONG_ULPLAN function [Miscellaneous], and SHORT_ULPLAN function [Miscellaneous] in Adaptive Server Anywhere SQL Reference Manual.
Enables or prevents users from accessing query plans from the Interactive SQL (dbisql) client or from using SQL functions to get plans.
ON, OFF
OFF
DBA permissions are required to modify this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.
When QUERY_PLAN_TEXT_ACCESS option is ON, users can view, save, and print query plans from the dbisql client. When the option is OFF, query plans are not cached, and other query plan-related database options have no affect on the query plan display from the dbisql client. The following error message displays:
No plan available. The database option QUERY_PLAN_TEXT_ACCESS is OFF.
“GRAPHICAL_PLAN function [String]”“
“QUERY_PLAN_TEXT_CACHING option”
“QUERY_DETAIL option,” “QUERY_PLAN_AS_HTML option,” “QUERY_PLAN_AFTER_RUN option,” and “OUTPUT statement” in the Sybase IQ Reference Manual.
PLAN function [Miscellaneous], EXPLANATION function, [Miscellaneous], GRAPHICAL_ULPLAN function [Miscellaneous], LONG_ULPLAN function [Miscellaneous], and SHORT_ULPLAN function [Miscellaneous] in Adaptive Server Anywhere SQL Reference Manual.
Allow users to specify whether or not Sybase IQ generates and caches IQ plans for queries executed by the user.
ON, OFF
OFF
DBA permissions are not required to modify this option. Can be set temporary for an individual connection. Takes effect immediately.
IQ query plans vary in size and can become very large for complex queries. Caching plans for display on the dbisql client can have high resource requirements. The QUERY_PLAN_TEXT_CACHING option gives users a mechanism to control resources for caching plans. If the user turns this option OFF, the query plan is not cached for that user connection.
If QUERY_PLAN_TEXT_ACCESS is turned OFF for a user, the query plan is not cached for the connections from that user, no matter how QUERY_PLAN_TEXT_CACHING is set.
“GRAPHICAL_PLAN function [String]”“
“QUERY_PLAN_TEXT_ACCESS option”
“QUERY_DETAIL option,” “QUERY_PLAN_AS_HTML option,” “QUERY_PLAN_AFTER_RUN option,” and “OUTPUT statement” in the Sybase IQ Reference Manual.
PLAN function [Miscellaneous], EXPLANATION function, [Miscellaneous], GRAPHICAL_ULPLAN function [Miscellaneous], LONG_ULPLAN function [Miscellaneous], and SHORT_ULPLAN function [Miscellaneous] in Adaptive Server Anywhere SQL Reference Manual.
