New migration capabilities help control database construction with respect to ROWID. The goal is to improve on ROWID performance by providing a direct, more naturalized, database-centric, approach to ROWID selection that reduces overhead to the database and can improve on overall transactional performance.
Based on inputs during migration, a processing algorithm attempts to generate purposed, intelligent selections for the primary, clustered, and ROWID indexes, in that order.
In this process, a primary key is typically "elected", the clustered index is "derived" out of the "push" phase of the migration, and the ROWID index is "derived" out of the "pull" phase of the migration. Through a combination of migration options and defaulting schemes, a best alternative for ROWID is chosen and loaded into the schema image and is then used at run-time to optimize server access for DataServer applications.
Ideally, the chosen ROWID index provides for the best overall application performance. While any unique index can make a good ROWID selection, as long as it contains the right properties and is a good "natural" key selection for your DataServer application's query criteria, some choices are better than others.
This criterion gives deference to natural key selection over the use of a surrogate key, since greater potential synergy can be gained between the ROWID index and the DataServer if the selected key also has business meaning. Progress recognizes that wide, highly mutable, excessively propagated natural key, or a key lacking significant business meaning cannot be a better alternative than a surrogate key. The ultimate decision on the best selection should go to those who understand their database objectives or have tested the overall application performance that any specific key selections can yield.
The following list identifies a general selection preference order for ROWID index criteria, in decreasing order of preference:
A ROWID generated from a selected unique and mandatory index whose clustered and primary indexes are explicitly or implicitly that same and whose characteristics closely resemble the clustered index guidelines discussed in Recommendation for choosing best clustered index.
A ROWID generated from a selected unique and mandatory index whose clustered and primary indexes are explicitly or implicitly that same.
A ROWID generated from a selected index with uniqueness, but without mandatory components that would otherwise supply a SQL primary.
A ROWID generated from a selected index whose uniqueness is appended to create ROWID candidacy.
A ROWID generated from a generated IDENTITY or from choosing Select Best whose index can be made clustered.
A ROWID generated from a generated IDENTITY or from choosing Select Best.
A ROWID generated from a selected unique and mandatory index whose clustered and primary indexes are explicitly or implicitly that same and whose characteristics closely resemble the clustered index guidelines discussed in Recommendation for choosing best clustered index.
