What's New in SQL Anywhere Studio
What's New in Version 8.0.0
New features in version 8
Adaptive Server Anywhere new features
Improved query processing This version includes enhancements to the query execution engine and the optimizer, resulting in a significant improvement in performance, especially for complex queries. Enhancements to Adaptive Server Anywhere query processing include the following:
More sophisticated internal processing of joins.
Improvements to the optimizer's cost model used to assess alternative access plans.
Improvements to the execution model.
Most of these changes are internal. Documentation is provided in Query Optimization and Execution.
An effect of these changes is that it is no longer the case that the materialization of results is necessarily inefficient. Use of temporary work tables may be a very efficient way to execute a query. For more information, see Use of work tables in query processing.
The optimizer now performs cost-based selection of indexes, and does not solely rely on predicate selectivities as was the case with prior releases.
Much of the improved query processing does not require an upgraded database. To use the new cost model on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
New index type A new type of index has been added that improves performance for multiple column indexes and for indexes that include wide columns. It is a compressed B-tree index.
Adaptive Server Anywhere automatically creates the appropriate type of index based on index width (the sum of the width of all columns in the index). A compressed B-tree index is created when the width of the index is greater than nine bytes and less than one-eighth of the page size to a maximum of 256 bytes; otherwise, Adaptive Server Anywhere creates hash B-tree indexes.
The WITH HASH SIZE clause of the CREATE INDEX statement is deprecated.
For more information about these indexes, see Types of index.
To use the new index types on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
A new limitation is imposed: foreign key indexes must have the same size and type as the corresponding primary key index.
dbunload now omits the hash size specification if it was originally specified with the default (WITH HASH SIZE 10).
New database option OPTIMIZATION_GOAL Determines whether query processing is optimized towards returning the first row quickly, or minimizing the cost of returning the complete result set. The default is to optimize for the first rows.
For more information, see OPTIMIZATION_GOAL option [database].
Performance enhancements for table scans Databases created in Adaptive Server Anywhere 8.0 with 2K, 4K, or 8K pages have performance-enhancements for queries that require sequential table scans. Adaptive Server Anywhere creates bitmaps, also known as page maps, for large tables. A bitmap lists all of the pages containing data for a given table. This feature permits searching large tables in only one I/O operation.
For more information, see Table and page sizes.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
Improved storage of checkpoint log The checkpoint log is now stored in consecutive pages at the end of the database file. This leads to improved performance by allowing sequential scans and multipage writes of the material in the checkpoint log.
For more information about the checkpoint log, see Checkpoints and the checkpoint log.
To gain the benefits of this enhancement on databases created before this release, you must upgrade the database file format by unloading and reloading the database.
Plan caching Adaptive Server Anywhere now caches execution plans for queries and INSERT, UPDATE and DELETE statements performed inside stored procedures, user-defined functions, and triggers. The maximum number of plans to cache is specified with the option setting MAX_PLANS_CACHED. To disable plan caching, set this option to 0.
For more information, see Access plan caching.
Overriding the default I/O cost model You can now override the default I/O cost model using the ALTER DATABASE statement with the CALIBRATE clause.
For more information, see ALTER DATABASE statement.
New database option MAX_PLANS_CACHED Sets the maximum number of execution plans that are stored in cache.
For more information, see MAX_PLANS_CACHED option [database].
New database option MIN_TABLE_SIZE_FOR_HISTOGRAM This option sets the minimum table size for which histograms are created. Histograms store information about the distribution of values in a column, and the optimizer uses them to choose an efficient execution plan.
For more information, see MIN_TABLE_SIZE_FOR_HISTOGRAM option [database].