SQL/MX 2.x Query Guide (H06.04+, J06.03+)

Reviewing Query Execution Plans

HP NonStop SQL/MX Query Guide—540437-005

4-9

Optimization Tips

NonStop SQL/MX relies on system-defined default settings for hundreds of attributes.

Most of these settings are finely tuned already. However, some attributes have values

that you can change.

You can change the default settings for externalized attributes in the

SYSTEM_DEFAULTS table in the system catalog. The settings are stored in the

NONSTOP_SQLMX_<systemname>.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS table.

You can also use the CONTROL QUERY DEFAULT command to change the values on

a per-process basis. For information about the precedence of the commands and the

ways you can specify the default settings, in addition to a listing of all externalized

attributes, see the SQL/MX Reference Manual.

This subsection provides information about certain optimization-related externalized

attributes. For reference information about each attribute, see the SQL/MX Reference

Manual.

•

GEN_EIDR_BUFFER_SIZE and GEN_PA_BUFFER_SIZE

These default settings determine the buffer size for PARTITION_ACCESS

operators. The settings must match for both attributes. These attributes provide

suggestions to the compiler, which will adjust the size of the buffer as necessary.

TOTAL_COST 2.0646531-002

DETAIL_COST CPU_TIME: 0.000287 IO_TIME: 0.020647 MSG_TIME: 0

IDLETIME: 0 PROBES: 1

DESCRIPTION

scan_type: file_scan SAMDBCAT.PERSNL.EMPLOYEE

scan_direction: forward

key_type: simple

lock_mode: not specified

access_mode: not specified

columns_retrieved: 6

fast_scan: used

fast_replydata_move: used

key_columns:

indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM)

executor_predicates:

(indexcol(SAMDBCAT.PERSNL.EMPLOYEE.SALARY)

40000.00) and

(indexcol(SAMDBCAT.PERSNL.EMPLOYEE.JOBCODE) =

450)

begin_key:

(indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) =

end_key:

(indexcol(SAMDBCAT.PERSNL.EMPLOYEE.EMPNUM) =

9999)

Column Name EXPLAIN statement Output