Setting the appropriate options helps you evaluate the query plan.
INDEX_ADVISOR – When set ON, the index advisor prints index recommendations as part of the Sybase IQ query plan or as a separate message in the Sybase IQ message log file if query plans are not enabled. These messages begin with the string “Index Advisor:” and you can use that string to search and filter them from a Sybase IQ message file. This option outputs messages in OWNER.TABLE.COLUMN format and is OFF by default.
See also the “sp_iqindexadvice procedure” in “System Procedures” in the Reference: Building Blocks, Tables, and Procedures.
INDEX_ADVISOR_MAX_ROWS – Used to limit the number of messages stored by the index advisor. Once the specified limit has been reached, the INDEX_ADVISOR will not store new advice. It will, however, continue to update count and timestamps for existing advice.
NOEXEC – When set ON, Sybase IQ produces a query plan but does not execute the entire query. When the EARLY_PREDICATE_EXECUTION option is ON, some portions of a query are still executed.
If EARLY_PREDICATE_EXECUTION is OFF, the query plan may be very different than when the query is run normally, so turning it OFF is not recommended.
QUERY_DETAIL – When this option and either QUERY_PLAN or QUERY_PLAN_AS_HTML are both ON, Sybase IQ displays additional information about the query when producing its query plan. When QUERY_PLAN and QUERY_PLAN_AS_HTML are OFF, this option is ignored.
QUERY_PLAN – When set ON (the default), Sybase IQ produces messages about queries. These include messages about using join indexes, about the join order, and about join algorithms for the queries.
QUERY_PLAN_TEXT_ACCESS – When this option is turned ON, you can view, save, and print IQ query plans from the Interactive SQL client. When QUERY_PLAN_ACCESS_FROM_CLIENT is turned OFF, query plans are not cached, and other query plan-related database options have no affect on the query plan display from the Interactive SQL client. This option is OFF by default.
See “GRAPHICAL_PLAN function [String]” and “HTML_PLAN function [String]” in Reference: Building Blocks, Tables, and Procedures.
QUERY_PLAN_AFTER_RUN – When set ON, the query plan is printed after the query has finished running. This allows the plan to include additional information, such as the actual number of rows passed on from each node of the query. In order for this option to work, QUERY_PLAN must be ON. This option is OFF by default.
QUERY_PLAN_AS_HTML – Produces a graphical query plan in HTML format for viewing in a Web browser. Hyperlinks between nodes make the HTML format much easier to use than the text format in the .iqmsg file. Use the QUERY_NAME option to include the query name in the file name for the query plan. This option is OFF by default.
QUERY_PLAN_AS_HTML_DIRECTORY – When QUERY_PLAN_AS_HTML is ON and a directory is specified with QUERY_PLAN_AS_HTML_DIRECTORY, Sybase IQ writes the HTML query plans in the specified directory.
QUERY_PLAN_TEXT_CACHING – Gives users a mechanism to control resources for caching plans. With this option OFF (the default), the query plan is not cached for that user connection.
If the QUERY_PLAN_TEXT_ACCESS option 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.
See also “GRAPHICAL_PLAN function [String]” and “HTML_PLAN function [String]” in Reference: Building Blocks, Tables, and Procedures.
QUERY_TIMING – Controls the collection of timing statistics on subqueries and some other repetitive functions in the query engine. Normally it should be OFF (the default) because for very short correlated subqueries the cost of timing every subquery execution can be very expensive in terms of performance.
Query plans can add a lot of text to your .iqmsg file. When QUERY_PLAN is ON, and especially if QUERY_DETAIL is ON, you might want to enable message log wrapping or message log archiving to avoid filling up your message log file. For details, see “Message log wrapping” in “Overview of Sybase IQ System Administration” of the System Administration Guide: Volume 1.