SQL/MP Query Guide

Contents
HP NonStop SQL/MP Query Guide—524488-003
v
4. Improving Query Performance With
Environmental Options (continued)
4. Improving Query Performance With Environmental
Options (continued)
Virtual Sequential Block Buffering (VSBB) 4-24
Comparison of Buffering Types 4-27
Requesting Buffering 4-28
Optimizing Sequential Access With Block Buffering 4-28
Effects of Cursor Operations on Performance 4-28
Controlling the Opening of Tables, Views, and Indexes 4-29
Controlling the Number of Key Columns Used by MDAM 4-30
Controlling MDAM’s Use of DENSE or SPARSE Algorithms 4-30
Controlling the Creation of NonStop SQL/MP Processes 4-31
Enhancing Sort Performance 4-32
Understanding Concurrency 4-32
Minimizing Overhead of Query Programs 4-33
5. Selectivity and Cost Estimates
How the Optimizer Estimates Selectivity 5-1
Computing Selectivity 5-2
Predicate Selectivity 5-3
Index Selectivity 5-6
Table Selectivity 5-7
Example Combining Predicate, Index, and Table Selectivity 5-8
Use of Default Selectivity Values 5-9
Join Selectivity 5-10
Grouping Selectivity 5-10
Assigning Cost to a Query 5-11
Cost of Accessing Tables
5-12
Cost of Physical I/Os
5-12
Cost of Record Overhead 5-13
Cost of Messages 5-13
Cost of Data Transfer 5-13
Cost of Subqueries 5-14
Cost of Sorts 5-14
Cost of Join Operations 5-15
The Effects of Indexes and Predicates on Costs 5-16
The Effect of the MultiDimensional Access Method (MDAM) on Costs 5-17
Evaluating Cost Estimates 5-19
How the Optimizer Chooses an Execution Plan 5-19
Forcing Execution Plans 5-20