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:

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 work tables in query processing (use All-rows optimization goal).

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.

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 Understanding 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 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.