Support for multiple indexes scan   The optimizer has been enhanced to consider multiple indexes (up to four) to retrieve data from a base table based on multiple predicates on that table. Previously, you could only specify one index as an index hint for a query. A new index hint in the WITH clause of the SELECT statement allows you to specify that a multiple index scan can be used. See FROM clause.

A new table access algorithm, Multiple Index Scan, has been added.

Support for index-only retrieval   The optimizer has been enhanced to support index-only retrievals. With index-only retrieval, the query is satisfied using data from the indexes, without having to access corresponding rows in the tables. The optimizer always performs an index-only retrieval when possible. An INDEX ONLY { ON | OFF } hint can be used to control whether index-only retrieval is performed. See FROM clause.

Load data from, and unload data to, files on a client computer   SQL statements and functions are used to read and write data residing on the database server. New features have been implemented to extend this capability to files that reside on the client computer, without the need to copy client files onto the database server. The transfer of data is accomplished efficiently, while providing security and access control for data on the client computer.

The actual reading of files on the client computer is done transparently by the client libraries, which means that existing client applications can start benefitting immediately from the new feature using the new SQL language support.

To benefit from these new capabilities, both the client and the database server must be SQL Anywhere version 11.0.0, and the client must use the Command Sequence communication protocol (CmdSeq).

See Access to data on client computers.

Unload data into a variable   The UNLOAD statement has been enhanced to include an INTO VARIABLE clause to allow you to unload data into a variable. See UNLOAD statement.

Load data from a column in another table   The LOAD TABLE statement has been enhanced to include a USING COLUMN clause to allow you to load data from a column in another table. See LOAD TABLE statement and Import data with the LOAD TABLE statement.

A database upgrade is required to take advantage of this new feature. See SQL Anywhere Server upgrades.

Load data from a value (BLOB)   The LOAD TABLE statement has been enhanced to include a USING VALUE clause to allow you to load data from a value expression, such as the results of a function or a system procedure. See LOAD TABLE statement, and Import data with the LOAD TABLE statement.

A database upgrade is required to take advantage of this new feature. See SQL Anywhere Server upgrades.

LOAD TABLE statement recovery and mirroring enhancements   Previously, in a mirrored database configuration, loading data from a file using the LOAD TABLE statement was not supported because only the LOAD TABLE statement was recorded in the transaction log, not the data being loaded. Additionally, when recovering a database, data loaded using a LOAD TABLE statement was not recoverable unless the original load file was present during recovery.

The LOAD TABLE statement has been enhanced to include three new logging option clauses: WITH CONTENT LOGGING, WITH ROW LOGGING, and WITH FILE NAME LOGGING. These clauses allow you to control whether to record the loaded data in the transaction log. In a database mirroring system, that data can be used to load the mirror database. Additionally, during recovery, the load file no longer needs to be present. See LOAD TABLE statement.

A database upgrade is required to take advantage of this feature. See SQL Anywhere Server upgrades.

Support for immediate materialized views   You can now configure materialized views to be refreshed immediately when data changes in the underlying tables impact data in the materialized view. Views with this refresh type are called immediate views; views that are not refreshed immediately are now referred to as manual views. Materialized views created before this release are considered manual views, and are the default when creating a new materialized view.

For more information about manual and immediate views, see Materialized views and Whether to set refresh type to manual or immediate.

You must upgrade your database to use the system procedures that support this feature. See SQL Anywhere Server upgrades.

Ability to refresh multiple materialized views at once   Previously, you needed to refresh materialized views one at a time. Changes to underlying data between each refresh operation could introduce inconsistencies between the materialized views. Now, to refresh multiple materialized views using the same data, you can specify a list of materialized views for the REFRESH MATERIALIZED VIEW statement. See REFRESH MATERIALIZED VIEW statement and Refreshing a manual materialized view.

New WITH SHARE MODE clause for the REFRESH MATERIALIZED VIEW statement   A new clause, WITH SHARE MODE, has been added to the REFRESH MATERIALIZED VIEW statement. This mode gives read access on underlying tables to other transactions while the refresh operation takes place. When this clause is specified, shared table locks are obtained on all underlying base tables before the refresh operation is performed. The default mode is now WITH SHARE MODE, unless the materialized view is defined as IMMEDIATE REFRESH, or snapshot isolation is enabled for the database. For more information about the default refresh behavior, see REFRESH MATERIALIZED VIEW statement.