SQL Query Optimization Messages › Bind-time Messages › Bind-time Summary Messages

Bind-time Summary Messages

Summary-level messages contain:

• Reference Information (source statement and indexes)
• Sort optimization
• Data Access Plan (join order and method, sorts required, total estimated cost)

Detail-level messages contain cost estimates for each possible combination of join order and method. If you think another join order or method should have a lower cost, you can see the estimates used by the optimizer.

Reference Information

This section identifies the statement and subselect or subquery being optimized and contains a copy of the SQL source statement and index definitions for referenced tables.

Headings and Source Statement

Bind-time messages begin by identifying the statement being bound and listing the SQL statement:

 *** Plan:authId.planName, Stmt:nnnnnnnn DT:yyyy.-mm-dd hh:mm:ss
 xxxxxxxxxxxxxxxxxxxxxxxxxxx source statement xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

One of the following messages identifies the subselect or subquery:

***** BIND MESSAGES FOR SUBSELECT nn *****
***** BIND MESSAGES FOR SUBQUERY LEVEL nn NUMBER nn  OF SUBSELECT nn *****

SUBSELECT

Subselects are numbered in the order they appear in the SQL statement. Unless you have a UNION, there is only one subselect.

LEVEL

Level 1 is a subquery of a subselect, level 2 a subquery of a level 1 subquery, and so on.

NUMBER

Multiple subqueries in the same search condition are numbered in the order in which they appear in the SQL statement.

Index Definitions

Index definitions are listed as reference information for each base table referenced in the query.

Table Level

  INDEX DEFINITIONS FOR: ttt/ddd authId.tableName correlationName
  KEYS=nn, IDXLVLS=n, LN=nnnnn, ROWS/BLK=nnn ROWS=nnnnnnnnn
  *** WARNING: INDEX CARDINALITY STATISTICS NOT COMPUTED ***

ttt/ddd

DATACOM 3-character name and database ID.

KEYS

Number of keys defined for the table.

IDXLVLS

Number of levels in the index.

LN

Number of bytes in a row.

ROWS/BLK

Number of rows that can fit in a data block (actual value may be higher for compressed areas).

ROWS

Number of rows in the table (as reported in the Directory (CXX)).

CARDINALITY NOT COMPUTED

Join optimization is very dependent on index cardinality. To compute index cardinality, use the CA Datacom/DB Utility (DBUTLTY) to either execute the RETIX or LOAD function, or use:

 REPORT AREA=IXX,DBID=nnn,TYPE=G,UPDATE=YES

Key Level

 KEY xxxxx id=nnn FLG=hh hh FLDS=nn DXX=nnnn BLKCHG=nnnn ROWS=nnnnnn

KEY

CA Datacom/DB five-character key name.

ID

CA Datacom/DB key ID.

FLG

Key attributes (as hex dump of bit flags):

Note: Any value found (on a report) that is unobtainable using the values below is for internal use only.

x'80'

NATIVE SEQUENCE KEY

x'40'

NIL-INCL-KEY

x'10'

MASTER KEY

x'08'

DUPLICATE KEY ID IN THIS FILE

x'04'

DIFFERENT TOTAL KEY LENGTHS

x'01'

UNIQUE KEY

x'20'

KEY HAS DATA TYPE SENSITIVE FIELDS

x'10'

KEY HAS DECIMAL FIELDS

x'08'

KEY IS RELATIVE RECORD NUMBER

x'01'

CBS WILL IGNORE THIS KEY

FLDS

Number of columns in key

DXX

Average number of index entries per DXX block

BLKCHG

Average number of data area blocks that must be accessed to read 1024 rows in sequence by this key.

ROWS

Number of index entries at the time when cardinality was last computed. This can be lower than ROWS at the table level if NIL-INCL-KEY is specified.

Column Level

 OFFSET=nnnnn, LN=nnn, DIR=xxxx SENS=x CARDINALITY=nnnnnnnnn xxxxx...

OFFSET

Offset of column in the row, relative to zero

LN

Number of bytes in the column

DIR

Either ASC for ascending or DESC for descending direction

SENS

Either Y for data type sensitive, or N

CARDINALITY

Number of unique values for this and preceding columns

xxxxx...

SQL column name

Sort Optimization

Each subselect or subquery can require at most two sorts for:

• SORT1: GROUP BY
• SORT2: ORDER BY, UNION, or DISTINCT

The estimated cost of these sorts is indicated by the following messages:

 ESTIMATED GROUP SORT COST= nnnnnnnnn
 ESTIMATED ORDER/DISTINCT/UNION SORT COST= nnnnnnnnn

If ORDER BY and GROUP BY reference the same columns, SORT1 eliminates the need for SORT2, which is indicated by the following message:

 ORDER/DISTINCT/UNION SORT SATISFIED BY GROUP BY SORT

UNION and DISTINCT always require SORT2, but if the scan index returns rows in sort sequence, then sorts for GROUP BY and ORDER BY can be eliminated.

The following messages indicate which indexes, if any, satisfy these sorts.

 KEY xxxxx SATISFIES GROUP BY
 KEY xxxxx SATISFIES ORDER/DISTINCT/UNION

However, if these indexes are to be used to eliminate sorts, they must:

• Index the first table in join sequence, and
• Be the same index as index merge, if index merge used.

Data Access Plan

The data access plan contains:

• Estimated cost
• Order and method in which tables are joined
• Scan indexes with which estimates were calculated (except for index-merge join indexes, and indexes used to eliminate sorting, different indexes may be used at execution-time due to the value of host variables or index statistics).
• Sorts required

Total Estimated Cost

Total estimated cost is given by the first message if there is a join, else by the second message.

 TOTAL JOIN COST ESTIMATE = nnnnnnnnn
 KEY xxxxx HAS LOWEST COST OF nnnnnnnnn FOR nnnnnnnnn ROWS

Join Steps

Messages in this section are only generated when a join exists.

The terms outer and inner table refererence the first and second tables of a join, respectively. When three or more tables are joined, the first two tables are joined, and the result of that join becomes the outer table of the next join, and so on.

Predicates dependent only on a single table are called restriction predicates. These predicates are always applied before join conditions. The terms inner and outer table refererence to conceptual intermediate tables containing only those rows of their base table for which restriction predicates are true.

JOIN STEPS:

Each join step is described in the order of execution. Different messages are issued depending on the join method used.

Nested-Loop Join Method

The nested-loop join method searches the inner table for matching rows for each outer table row. A matching row is a row for which the join conditions are true.

Nested-loop is used when:

• Merging is not possible.
• Has the lowest estimated cost.
• Manual optimization is specified with plan option MSG.

An example of when nested-loop has the lowest estimate cost:

• Outer table has a non-join index restriction (merge would need to read the entire unrestricted join index), or a non-join index can eliminate a sort due to GROUP BY or ORDER BY.
• Inner table has an index restricted by the join condition (the entire table does not need to be searched).

   NESTED-LOOP JOIN TBL n USING KEY xxxxx
   TO TBL n USING KEY xxxxx
  

TBL

Indicates table by its position in the FROM clause.

KEY

CA Datacom/DB five-character key name that has the lowest estimated cost. This may not be the key used at execution time.

TO

Only the first join generates both lines. The outer table of subsequent joins is the intermediate result table of previous joins. This intermediate result table is not materialized, that is to say, it is not physically generated as a temporary table in the TTM area.

Index Merge Join Method

Index merge can only be used when:

• A first join.
• There is at least one equijoin condition.
• There are no non-equijoin join conditions.
• Operands of equijoin predicates are column references (no expressions or scalar functions) but data type conversion is acceptable.
• Both tables have a matching index with all join columns in the same order.
• No OR has more than one table under it.
• There are no expressions in the search condition referencing more than one table.

An example of when index merge has the lowest cost:

• Search condition contains only equijoin predicates (no restrictions).
• Restriction predicates do not reference indexed columns.

Under these conditions index merge has the following advantages over nested-loop:

• Only one set is required for the inner table. Nested-loop requires a set for each outer table row.
• Since there is only one inner table set, it is usually larger and benefits more from pre-fetch.

   INDEX-MERGE JOIN TBL n USING KEY xxxxx TO TBL n USING KEY xxxxx
  

TBL

Table is identified by its position in the FROM clause.

KEY

CA Datacom/DB five-character key name of index used for the merge.

Sort-Merge Join Method

The sort-merge join method has the same restrictions as index-merge except that no matching indexes are required. Instead, the tables are sorted in the sequence the matching indexes would have provided before merging begins.

These sorts build temporary tables in the TTM area.

An example of when sort-merge is faster than nested-loop:

• There is no index on the inner table restricted by the join predicates. Nested-loop must read the entire inner table for each outer table row, which is the entire base table unless there are restrictions that restrict an index scan range.
• Non-indexed restrictions exist on the inner table. Nested-loop would need to read rows rejected by the restriction for each outer table row, but sort-merge only rejects these rows once on input to the sort.

   SORT TBL n FOR SORT-MERGE JOIN
   SORT TBL n FOR SORT-MERGE JOIN
   SORT-MERGE JOIN TBL n TO TBL m
  

TBL

Table is identified by its position in the FROM clause.

Sorts Required

The following messages indicate if a sort is eliminated by using an index:

  KEY xxxxx USED FOR GROUPING
  KEY xxxxx USED FOR ORDERING

If a sort is required, its reasons are given in the following messages:

 GROUP BY ........ SRT REASONS= xxxxxxx
 ORDER BY/DISTINCT SRT REASONS= xxxxxxx

CBS ORDER

The Compound Boolean Selection Facility cannot return rows in sort sequence because no index satisfies sort sequence.

EXPR/FUNC

ORDER BY refers to a SELECT list column that is an expression or function.

MULTI-TBL

ORDER BY or GROUP BY refers to columns in multiple tables.

JOIN

Index-merge join method is used. Its index does not satisfy ordering requirements.

DISTINCT

DISTINCT specified.

UNION

UNION (without ALL) specified.

Predicates Evaluated in SQL-Subsystem

The following message indicates how many predicates are evaluated by the SQL subsystem:

***** nn PREDICATES EVALUATED BY SQL SUBSYSTEM IN JOIN STEP nn *****

These predicates cannot restrict index scan range, or be evaluated from the index; so, they cause data scanning.

If the data is remote, rows may be transferred to the requesting node only to be rejected by these predicates.

The following predicates are evaluated by the SQL subsystem:

• When a column is compared to a literal, host or register variable that has a greater length, scale, or whole number digits.
• LIKE, other than pattern xxx%.
• IN list.
• Comparing two columns in the same table with different data types, precision, scale, (although different data type modifiers type-numeric, sign, and nullable are permitted).
• Correlated or quantified subquery.
• Predicates with operands that are expressions or scalar functions (except concatenation of contiguous not nullable character columns, and arithmetic with only literals).
• Predicates under an OR containing reference to more than one table.
• Index or sort merge equijoin predicates.
• Predicates whose operands make no reference to a column.
• All predicates in the HAVING search condition.

You can dump the actual predicates by using the CA Datacom/DB Utility (DBUTLTY) request:

 COMM OPTION=ALTER,TRACE=TRACECRS,JOBNAME=xxxxxxxx

If you cannot convert a predicate into a type that restricts index scan range or be evaluated from the index, you only improve efficiency slightly or make it worse by converting it to a predicate that the Compound Boolean Selection Facility (CBS) evaluates.

For example, converting the predicate:

  "COL1 IN(&HOST1., &HOST2.)"

to

 "COL1 = :HOST1 OR COL1 = :HOST2"

would not help much unless COL1 is in the scan index. Performance could be worse if there are many other predicates ANDed with this predicate that are evaluated by Compound Boolean Selection. This is because these other predicates are repeated for each IN list entry to be in the CBS-required disjunctive normal form.