NonStop Systems Introduction

The Relational Database Management System

NonStop Systems Introduction—527825-001

4-5

High-Speed Indexes for Faster Access to Data

Each row of a SQL/MX table is identified by a column or group of columns known as

the primary key. The primary key value must be unique for each row, so that SQL/MX

retrieves data from only that row when the primary key value is specified in a query.

However, if end users or applications frequently access a table by some column value

other than the primary key value, you can speed the execution of such queries by

creating an index on this alternate column.

For example, if the primary key of the EMPLOYEE table is EMPNUM (employee

number), but users typically supply LNAME (last name) and FNAME (first name) when

making queries, you can create an index on these two values. Then, when a user

supplies these values to retrieve data about an employee, SQL/MX uses the index to

locate the correct row instead of scanning the entire table in primary key order until it

finds the correct row.

Each index has a name and is stored in a key-sequenced file. Index files are not

tables; they are objects used by SQL/MX to provide faster access to tables.

Parallel Operations for High Performance

SQL/MX can execute large queries that scan large portions of the database in parallel.

These large queries are typical of DSS applications, in which users might need to

examine large subsets of data to make complex decisions. To perform these large

queries, SQL/MX performs parallel processing within a query.

Although SQL/MX supports several basic forms of parallel processing, partitioned

parallelism is the main type of parallelism in SQL/MX. Partitioned parallelism is the

ability to divide the data to be processed into partitions and work on each one in

parallel. In a partitioned parallel plan, multiple operators all work on the same plans,

and results are merged by using multiple pipelines.

The ability to perform parallel processing results in exceptionally high performance.

Clustering for Scalability

SQL/MX is optimized for clustered environments. The database deploys tasks evenly

across every processor in a cluster. As you add processors or systems to a cluster, the

database scales linearly; for example, a dual-processor system yields twice the

performance of a single processor, a three-processor system yields three times the

performance of a single processor, and so on.

SQL/MX clustering is completely transparent to users; even though database access

takes place on separate processors in the cluster, you manage SQL/MX as if you were

managing a single database on a single system.