Base Tables
ACTIVITY FOR TBL <authId.tblName correlation name> SETS=nnnnnnnnn INDEX=nnnnnnnnn DATA=nnnnnnnnn QUAL=nnnnnnnnn I/O=nnnnnnnnn
See the table level summary message for the definition of these fields.
CBS OPTIMIZER REASONS: xxxxxxxxxxxxxxxxxxxxxx
Compound Boolean Selection (CBS) Optimizer Reasons indicate the type of processes used and why they were necessary.
For example, there is a query that you do not think should require index or data scanning, but INDEX and DATA is approximately twice QUAL. This can be explained if index merging is used because it builds a temporary index, where each data row may be accessed one or more times to build the temporary index, and then using the temporary index, read once more to return rows after duplicates have been eliminated. You can determine if index merging was indeed used from CBS Optimizer Reason number 4.
See the CBS Diagnostic Report description in the CA Datacom/DB DBUTLTY Reference Guide. for the definition of the optimizer reasons.
Sort Temporary Tables
SORT COMPLETED: ROWS EST=nnnnnnnnn IN=nnnnnnnnn, OUT=nnnnnnnnn
Number rows estimated, which may be zero if an estimate was not required.
Number rows input to the sort.
Number rows output by the sort. If GROUP BY or DISTINCT is specified, this can be a lower number than rows input to the sort because the sort process performs grouping and eliminates duplicate rows.
HIGHEST SORT MERGE LEVEL = nn
The sort first builds strings of sorted rows, then after several strings are built, the strings merged into a longer string. After several of these longer strings are built, they are in turn merged into yet a still longer string. This process is continued until there is a single result string. Each merging of strings is called a merge level. As you can see, the cost per row of a sort is dependent on the number of these merge levels.
Quantified Subquery
ACTIVITY FOR TEMP TBL **SUBQUERY nn ** ** SUBQUERY nnn ** TBLS=nnnnnnnnn ROWS=nnnnnnnnn READS=nnnnnnnnn
Number within SELECT block
Number of times temporary table was built, that is to say, number of times subquery was executed. This will of course be 1 or 0 unless subquery is correlated.
Number of rows in table. If TBLS is greater than 1, this is the sum of all the rows in all tables.
Number of rows read from table. This can be greater than the number of rows if subquery is not correlated.
|
Copyright © 2014 CA.
All rights reserved.
|
|