SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-3
Minimizing Contention
During a CREATE INDEX...WITH SHARED ACCESS operation, SQL sets the
AUDITCOMPRESS option to OFF for the base table. Therefore, during the CREATE
INDEX operation the audit trail grows at a faster rate than it does when
AUDITCOMPRESS is ON (the default). More audit trail space is needed when the
AUDITCOMPRESS option is OFF; the amount depends on the intensity of write activity
during the CREATE INDEX operation.
After the CREATE INDEX operation completes, the AUDITCOMPRESS option is set to
its original value.
When you specify the WITH SHARED ACCESS option, these steps occur:
1. Initialization and load. SQL reads catalog entries for existing (source) objects
involved in the operation and creates the new (target) objects for the operation.
SQL then begins copying data from the source objects to the target objects.
2. Audit fix-up. Audit fix-up processes search TMF audit trails for any changes made
since the load of the records. If changes are found, the target objects are updated
to reflect the changes. For index creation, SQL transforms the data as needed.
At this point, operation depends on COMMIT options selected with the DDL
statement:
•
If a BEFORE or AFTER time was specified, SQL waits for the appropriate time
window before starting the next (commit) phase. If the time window has
passed, SQL performs as specified by the value of the ONCOMMITERROR
clause.
•
If [BY] REQUEST was specified, SQL issues a warning to notify the user that
the operation is ready, and waits for the user to respond with a CONTINUE
statement. At this point, the user sees a “D>” prompt in the SQLCI session.
The user can continue the operation, request a rollback, or enter other SQLCI
commands except CATALOG, SYSTEM, VOLUME, EXIT, DEFINE-related
commands, or a DDL or utility command against the same object as the
ongoing DDL operation.
While the operation waits, the audit fix-up processes continue reading audit trails
and updating target objects.
3. Commit. SQL acquires an exclusive table lock on each source object and searches
audit trails for any changes made since the last audit fix-up work. SQL updates the
target objects to reflect the changes. Finally, SQL updates file labels and catalog
files. At this time, exclusive locks are obtained on the other partitions.
For more detailed information, see the “WITH SHARED ACCESS Option” in the
SQL/MP Reference Manual.
If you do not specify the WITH SHARED ACCESS option, Step 1 and Step 3 are
performed, but the audit trail is not searched in Step 3.