SQL/MP Query Guide
HP NonStop SQL/MP Query Guide—524488-003
2-1
2 The Optimizer
The NonStop SQL/MP optimizer is a component of the SQL compiler. The optimizer
plays an important role in the high-performance operation of SQL by selecting the most
efficient access plan for a query. During compilation, the optimizer examines each data
manipulation statement and generates query execution plans (also called access
plans) to retrieve the requested data from the database. The optimizer also examines
statistics in the catalog for each column referenced by the statement; if statistics are
current, the optimizer can choose an efficient query execution plan to retrieve the
required data.
For example, a plan for a query that references a single table consists of a strategy for
accessing the table using a specified index and begin and end keys. For a query that
references multiple tables, an execution plan also specifies the order in which the
tables should be accessed.
This section contains these topics:
•
SQL Components and the Optimizer on page 2-1
•
How the Optimizer Chooses an Execution Plan on page 2-3
To evaluate the performance of an existing query, see Section 6, Analyzing Query
Performance.
Issues of database design, tuning, and ongoing maintenance are not discussed in this
manual. For more information on these topics, see the SQL/MP Installation and
Management Guide.
SQL Components and the Optimizer
These components play a role in executing an execution plan provided by the
optimizer:
•
SQL executor
•
File system
•
Disk process
SQL statements invoke the SQL executor, a set of library routines that run in the
application’s process environment. The executor invokes the file system. The file
system accesses each table required by the query execution plan by sending
messages to the appropriate disk processes.
Message traffic is reduced by filtering data at its source. Data can be returned to the
file system one row at a time or can be buffered and returned as a block by the disk
process.
Figure 2-1 on page 2-2 shows the components involved in the execution of an
interactive SQL query using SQLCI.