I’d rather play golf

In recent weeks we have been working with a few customers who are migrating from older SQL Anywhere releases to SQL Anywhere 11. Moving from older releases (9 and below) to Version 11 requires a complete rebuild of the database file. This is because in SQL Anywhere Version 10 we changed the the storage layout of the database file to support some important new features, such as snapshot isolation, that simply could not be supported with the existing storage format.

Performing the unload/reload as part of a database version migration is an opportunity for DBAs to re-consider their indexes, particularly those indexes that support referential integrity constraints. In this regard, there are two significant changes in SQL Anywhere 11 to be aware of.

Changes to primary keys

The first behaviour change is the ordering of columns with composite (multi-column) primary key indexes. In SQL Anywhere version 9 and below, it was a requirement – due to continued support of legacy database formats – that all primary key columns appear at the beginning of each row. Moreover, it was the column order in the table that specified the ordering of the columns in the primary key index, and not the ordering of the columns in the PRIMARY KEY clause.

For example, suppose one had this table specification in a Version 9 database, which I’ll call pktest.db:

1
2
3
4
5

CREATE TABLE testPKs
   ( x INTEGER NOT NULL,
     y INTEGER NOT NULL,
     z INTEGER NOT NULL,
PRIMARY KEY (z, y, x ) )

The ordering of the columns in the primary key index in this Version 9 database would be (x,y,z), corresponding to the order of the columns in each table row. Starting with Version 10, however, the primary key index order will be (z,y,x), matching the PRIMARY KEY specification.

If one migrates a Version 9 database to a Version 11 database using the documented dbunload utility, this change in behaviour is handled seamlessly:

6

dbunload -c "dbf=pktest.db;uid=dba;pwd=sql" -an pktest11.db

The unload proceeds as follows:

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SQL Anywhere Unload Utility Version 11.0.1.2302
Connecting and initializing
Unloading user and group definitions
Unloading table definitions
Unloading index definitions
Unloading functions
Unloading view definitions
Unloading procedures
Unloading triggers
Unloading SQL Remote definitions
Unloading MobiLink definitions
Creating new database
Unloading "DBA"."testPKs"
Creating indexes
Creating indexes for (1/1) "DBA"."testPKs"

The table “testPKs” is table ID 704 in the new pktest11.db database (found by querying the SYSTABLE catalog view). We can then verify that the primary key index corresponds to the actual physical index characteristics from the original Version 9 database by querying the catalog in the new Version 11 database:

22
23
24
25
26
27
28

SELECT i.table_id, i.index_name, ic.SEQUENCE, ic.column_id, 
       c.column_name, ic."order" 
FROM SYSIDX i JOIN SYSIDXCOL ic ON (i.table_id = ic.table_id) 
     JOIN SYSCOLUMN c ON (ic.table_id = c.table_id 
                         AND ic.column_id = c.column_id)
WHERE i.table_id = 704
ORDER BY ic.column_id

which gives the following result:

However, if one rebuilds a Version 9 database manually, using separate rebuild scripts, the Version 11 server will create the primary key index according to the PRIMARY KEY specification and this may result in a different index than that which existed in the Version 9 database.

One more thing: in Version 11 one can specify the sorting sequence for each column in a primary key index, and explicitly specify that the primary key index is CLUSTERED. This means that in Version 11 one could specify:

29
30
31
32
33

CREATE TABLE testPKs
   ( x INTEGER NOT NULL,
     y INTEGER NOT NULL,
     z INTEGER NOT NULL,
PRIMARY KEY CLUSTERED (z DESC, y ASC, x DESC ) )

which, depending on the access patterns of your application, permit you to eliminate outright an entire index – perhaps even more than one – on that table.

Enhancements to FOREIGN KEY specifications

SQL Anywhere 11 also contains enhancements to FOREIGN KEY support. With Version 11, one can specify referential integrity constraint options that include:

• how to handle NULL values with multi-column foreign keys by specifying either MATCH SIMPLE or MATCH FULL;
• whether or not the foreign key is CLUSTERED;
• whether or not the foreign key should be enforced only at the end of a transaction (CHECK ON COMMIT);
• what referential actions to enforce (the defaults are ON UPDATE RESTRICT and ON DELETE RESTRICT);
• specifying ASC or DESC to the FOREIGN KEY column specification, so that the index generated for the foreign key may be in ascending or descending sequence;
• the ability to specify UNIQUE, so as to generate a foreign key that embodies a strict 1:1 relationship without the need for an additional UNIQUE constraint (and index);
• the ability to specify a column ordering for the foreign key that does not match the primary key, enabling complete customization of the index constructed for the foreign key.

The benefit of these enhancements is the ability to customize the physical characteristics of indexes that are maintained to support referential integrity constraints, and offer the possibility of eliminating now-redundant indexes that were previously needed to provide alternative orderings.