The following options can help you evaluate the query plan. See the Sybase IQ Reference Manual for details of these options.
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 the Sybase IQ Reference Manual.
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 query, except when the EARLY_PREDICATE_EXECUTION option is ON.
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_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 set ON and a directory is specified with QUERY_PLAN_AS_HTML_DIRECTORY, Sybase IQ writes the HTML query plans in the specified directory.
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 will probably want to enable message log wrapping by setting IQMSG_LENGTH_MB to
a positive value.
