Adaptive Server 15.0 has a new optimizer. Most queries run on this optimizer should run the same or better with few or no changes. However, test all applications, before you use the server in production, for the following issues:
Because of changes
in the parser, some queries may return a general syntax error (message
102) instead of Syntax error at line # (message
156).
The maximum number of worktables per query increased from 14 to 46.
Adaptive Server 15.0 orders a result set differently from earlier versions unless there is an explicit order by clause in the query:
Earlier releases returned result sets in sorted order when you used a group by clause, even without an order by clause. The grouping algorithm created a worktable with a clustered index, and grouping is based on inserting into this work table.
In Adaptive Server 15.0, the grouping algorithm uses a hash-based strategy, which does not generate a sorted result set. To generate a sorted result set, change queries to include an order by clause. This change complies with ANSI SQL standards.
Adaptive Server 15.0 includes alternative algorithms for relational operations: hash- or merge-based union, hash- or sort-based distinct, and so on. The execution plan determines the ordering of the result set of a query without an order by.
Adaptive Server 15.0 increases the amount of time for query compilation because the query processing engine looks for more ways to optimize the query. However, there is a new timeout mechanism in the search engine that can reduce the optimization time if there is a cheaper plan.
Adaptive Server 15.0 deprecates the enable sort-merge join and JTC configuration option, so you may see the query engine using merge joins unexpectedly. You can force the query engine to use the behavior from earlier versions, but the new in-memory sort operations improve the sort merge join performance. The query optimizer does not use merge joins if they are inappropriate. You can disable sort merge join at the session level with the set merge_join 0 command, or disable it with the allrows_oltp optimization goal.
Adaptive Server 15.0 allows you to disable reformatting at the session level with the set store_index 0 command.
See Chapter 17, “Managing User Permissions” in System Administratin Guide, Volume One for more information about using login triggers in Adaptive Server to modify the session level settings without modifying applications.
Adaptive Server 15.0 improves algorithms that organize joins between compatible, but different, data types. You can use indexes even if the SARGs use different datatypes. However, do not use trace flag 291, which gives spurious erroneous result sets.
Adaptive Server 15.0 does not support set statistics io for parallel queries, but is supported for nonparallel queries.
You can get an accurate interpretation of the statistics by setting set statistics plancost on, which shows the parallel access nodes in the Lava operator tree.
See Query Processing in Adaptive Server for details about query processing in Adaptive Server 15.0.
