SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Reviewing Query Execution Plans

HP NonStop SQL/MX Query Guide—523728-003

4-9

Optimization Tips

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 change the values on a per-process basis with the CONTROL QUERY

DEFAULT command. 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 related information about each attribute, see the SQL/MX

Reference Manual.

•

JOIN_ORDER_BY_USER

This attribute provides an easy alternative to CONTROL QUERY SHAPE that you

can use when you want to specify the order of the tables but not the join type. The

first table specified in the FROM clause is the outer table in the query.

TOTAL_COST 4.1293062E-002

DETAIL_COST CPU_TIME: 0.000433 IO_TIME: 0.041293 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 DISPLAY_EXPLAIN Output