Sybase 12.4.2 manual Planning queries, Query evaluation options, 457

CHAPTER 12 Managing System Resources

•Join indexes typically cause join queries to execute faster than ad hoc joins, at the expense of using more disk space. However, when a join query does not reference the largest table in a multi-table join index, an ad hoc join usually outperforms the join index.

•You can improve performance by using an additional column to store frequently calculated results.

Planning queries

If you have created the right indexes, the Adaptive Server IQ query optimizer can usually execute queries in the most efficient way—sometimes even if you have not used the most effective syntax. Proper query design is still important, however. When you plan your queries carefully, you can have a major impact on the speed and appropriateness of results.

Before it executes any query, the Adaptive Server IQ query optimizer creates a query plan. Adaptive Server IQ helps you evaluate queries by letting you examine and influence the query plan, using the options described in the sections that follow. For details of how to specify these options, see the Adaptive Server IQ Reference Manual.

Query evaluation options

The following options can help you evaluate the query plan. All of these options are OFF by default.

•IQ_QUERY_PLAN_ONLY When you set this option ON, the query optimizer dumps the query plan into the log transaction file rather than submitting it to the query engine.

•QUERY_INFORMATION When you set this option ON, Adaptive Server IQ produces messages about queries. These include messages about using join indexes, about the join order, and about join algorithms for the queries.

•QUERY_DETAIL When you set this option ON, Adaptive Server IQ displays additional information (as part of the QUERY_INFORMATION option) about the query when producing its query plan. When QUERY_INFORMATION is OFF (the default), this option is ignored.

457