Introduction to NonStop SQL/MP

What Happens When a Query Is Submitted
NonStop SQL Architecture
3–12 113425 Tandem Computers Incorporated
Figure 3-5. Selecting an Access Plan With the Lowest Execution Cost
013
SQL OptimizerQuery
Plan A
Read Table A
Plan B
Read Index to
Access Table A
Plan C
(Parallel Execution)
Read Index Partitions
in Parallel
Access Table A
Partitions in Parallel
Execution cost = 12 Execution cost = 8 Execution cost = 4
When it chooses an access plan, the optimizer analyzes the relationships between the
query elements and the database configuration. For example, the optimizer may
choose to read an index if that will result in scanning fewer rows than reading the base
table directly.
Consider the following query:
SELECT EMPNUM, LAST_NAME,
FIRST_NAME,DEPTNUM
FROM EMPLOYEE
WHERE LAST_NAME LIKE "MACDONALD%" ;
For this plan, the optimizer uses the XEMPNAME index (discussed earlier in this
section) because the condition in the WHERE clause refers to an index column,
LAST_NAME, and narrows the selection to a relatively few rows—only those
containing employees named MacDonald. (The LIKE predicate compares character
strings, and the percent sign is a wild-card symbol permitting any characters in that
position.)
If the XEMPNAME index were not used, NonStop SQL/MP would have to read the
entire EMPLOYEE table to find all employees named MacDonald. Thus, the
selectivity, or percentage of rows in a table that satisfy a search condition, is important
in query optimization.
If a query selects from more than one table or a view derived from more than one
table, the optimizer chooses a strategy for joining the tables. If a query contains
subqueries, the optimizer selects the best order for executing the subqueries. If sorting
the data speeds up access, the optimizer determines how to sort the data. If a table is
partitioned across many disk volumes managed by different processors, the optimizer
determines whether to partition work among the different processors.