Introduction to NonStop SQL/MP

Additional Features

113425 Tandem Computers Incorporated 1–15

Additional Features The following additional features help make NonStop SQL/MP a powerful tool for

large applications requiring high performance, high availability, scalability, and data

integrity:

Cost-based query optimization

Locking

Mixed workload environment

Support for national languages

Active data dictionary

Constraints

Performance features for DSS

Tools for database administration

FastSort sorting utility

Cost-Based Query

Optimization

The SQL optimizer, a component of the SQL compiler, determines the best (and

quickest) way to retrieve the data you want. The optimizer examines the SQL query

you have given it and combines this information with information about the physical

database configuration. For example, the optimizer evaluates the size of tables,

whether the tables are local or remote, and which indexes on the tables are available.

Based on this information, it evaluates several alternatives and chooses the execution

plan that will use the fewest system resources.

As an end user, you do not need to worry about the order in which you refer to tables

and conditions, or whether an index exists that might improve the performance of the

query. You simply issue the query, and the optimizer develops the plan that will

execute your request most efficiently.

Locking If an application has hundreds or thousands of users, it is likely that several users will

want to access or modify the same data at the same time. In such cases it is important

to protect the integrity of the data so that unexpected changes do not occur.

Suppose you send your bank a change of address form and one clerk updates your

address while another clerk is updating your account balance. Without some controls,

the clerk who is updating your account balance could accidently overwrite your new

address with the old one. Now suppose a bank manager simultaneously queries the

customer account database to compare levels of business activity at various branches.

If possible, the database should remain available to this read-only query even while

update controls are in effect.

NonStop SQL/MP provides several mechanisms to ensure that multiple users do not

create surprises for one another. Moreover, these locking mechanisms allow

NonStop SQL/MP to sustain both OLTP and DSS workloads—that is, both updates

and read-only access.