Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere 11 - Changes and Upgrading » What's new in version 11.0.0 » SQL Anywhere » SQL Anywhere new features

 

Main features

Following is a list of the main features introduced in SQL Anywhere version 11.0.0.

  • Support for merging tables   SQL Anywhere now allows you to merge tables, views, and system procedure results into a table or view. See MERGE statement.

  • Support for login policies   SQL Anywhere now supports login policies. A login policy is a set of options that define rules to be applied when a database connection is established for a user. SQL Anywhere supplies a root policy used to store default values for all login policies. Separate login policies can be created to store overrides to the root login policy options. Each user is assigned a login policy and does not inherit any of the login policies through group memberships. The assignment of a policy to a specific user can be modified as necessary. See Managing login policies overview.

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

  • Support for full text search   SQL Anywhere now supports full text search. Full text search can quickly find all instances of a term (word) in a database. Full text search differs from searching using predicates such as LIKE, REGEXP, and SIMILAR TO because it is term-based and because it uses a text index instead of scanning table rows. See Full text searching.

    You must upgrade your database to make use of the full text search feature. See Upgrading SQL Anywhere.

  • Support for regular expressions   SQL Anywhere now provides support for regular expressions using two new search conditions, REGEXP and SIMILAR TO. See REGEXP search condition and SIMILAR TO search condition.

    See also, Regular expressions overview and LIKE, REGEXP, and SIMILAR TO search conditions.

  • Database option settings are now recorded in the transaction log   The database option settings in effect during a LOAD operation are now recorded in the transaction log. This ensures that there are no inconsistencies in the data should options, such as date_order and nearest_century, change between the original LOAD operation and the final LOAD operation resulting from applying the transaction log during recovery.

  • Enhancements to the optimizer's use of indexes   Several enhancements have been made to the indexing capabilities of SQL Anywhere. You must upgrade your database to make use of these new features. See Upgrading SQL Anywhere.

    • 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. See MultipleIndexScan method (MultIdx).

    • 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 Indexes can be used to satisfy a predicate and FROM clause.

  • Enhancements to loading and unloading data   The following enhancements have been made to loading and unloading data:

    • Load data from, and unload data to, files on a client computer   The database server already provided support for reading and writing data residing on the database server computer, by means of SQL statements and functions. 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 computer. 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 Accessing 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 Upgrading SQL Anywhere.

    • 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 Upgrading SQL Anywhere.

    • 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 Upgrading SQL Anywhere.

  • Enhancements to materialized views   Support for materialized views has been enhanced as follows:

    • 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 Working with materialized views and Manual and immediate materialized views.

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

    • 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 Refresh manual views.

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

  • Support for querying the contents of a file or BLOB string   Using the new OPENSTRING subclause of the FROM clause, you can now query data from a file or a BLOB string. The OPENSTRING clause allows you to specify the object to be queried, as well as the schema and other parsing information for the data. See FROM clause.

    A new plan item, OpenString, appears in the execution plan when the OPENSTRING operation is performed. See OpenString algorithm (OpenString).

  • Improved support for compressed indexes   Because of the work to improve support for compressed indexes, when you rebuild a database by unloading and reloading it, the rebuilt database may be smaller than the original database. This decrease in database size does not indicate a problem or a loss of data.

  • Read-only access to databases running on a mirror server   If you are using database mirroring, you can now connect to the database running on the mirror server. This enables you to offload potentially resource-heavy reporting operations to the mirror server, while leaving the primary server available. You can connect to a mirror database by providing a database server name with the -sm server option that can be used to access the read-only mirror database. See -sm database option and Configuring read-only access to a database running on the mirror server.