SQL/MP Query Guide

The Optimizer

HP NonStop SQL/MP Query Guide—524488-003

2-3

SQL Executor

The executor is a set of system library procedures that executes compiled DML

statements against database tables, views, or the database catalogs. To execute a

DML statement, the executor uses the query execution plan generated and optimized

by the SQL compiler.

The executor maps logical names to physical names, obtains rows from various tables

by using the file system, joins tables, sorts where required, and returns the result to the

SQLCI process or to the host variables in a user program.

File System

The file system, which also resides in the system library, handles the opening of tables

and indexes, partitioning, sending requests to appropriate disk processes, and

buffering of replies, inserts, and updates. When a table is updated, the file system

manages the updates to a table and all its alternate indexes.

Disk Processes

Disk processes manage disk space, access paths, locks, log records, and a main

memory buffer pool of recently used blocks called the cache. Disk processes can also

evaluate predicates, aggregates, groupings, and table constraints. Each disk process

authorizes the application process to access the table when the file system sends an

OPEN request.

Each disk volume is managed by a set of disk processes, which have a common

request queue and a shared cache.

How the Optimizer Chooses an Execution Plan

Before determining the most efficient access plan, the optimizer performs query

transformations, if possible, to make the query more efficient and then evaluates these:

•

An access path (primary key, index, index-only) to each table:

In primary access, rows are read directly from the base table, without using an

alternate index.

In index access, an alternate index is used to access the base table. An

alternate index is a key-sequenced file containing a copy of selected columns

of every row of the base table. The columns of an index consist of the index

key columns plus the primary key columns. After reading a row from an index,

the file system can use the primary key within the index to read the

corresponding row of the base table.

Index-only access occurs if all of the needed columns are already present in

the index row. In such a case, there is no need to access the base table. SQL

can retrieve the data directly from the index.