The detail-level bind-time messages show the cost estimates for each combination of join order and method.
Join optimization messages are produced in the following way. Each level of indentation indicates a loop at that level:
The join order with the lowest estimated cost is repeated at the end after the following heading:
---------------------- LOWEST COST CANDIDATE ---------------------
Note: Because of the number of rows generated, do not use this option when joining more than 6 tables.
Restriction Costs
TBL xxx DBID nnn RESTRICTION COSTS:
This is the estimated cost without join conditions, that is to say, as if this table were the outer table of the first join. Restriction cost estimates are computed once before all possible join orders are estimated.
Join Step Detail
CANDIDATE JOIN SEQUENCE = n,n,n.... JOIN STEP n
The cost of all possible join orders is estimated, until the estimated cost exceeds a previously computed estimate. The numbers refererence table positions in the FROM clause.
The first two tables are joined in join step 1. The output of this step is joined to table three, and so on. The cost of each join step is estimated separately.
Nested-Loop
NESTED LOOP JOIN COSTS:
KEY xxxxx 1ST nn FLDS SELECTIVITY= .nnnnnnnnn
nn LOW-ORDER FLDS SELECTIVITY= .nnnnnnnnn
DATA SELECTIVITY= .nnnnnnnnn
INDEX nnnnnnnnn DATA nnnnnnnnn SORT nnnnnnnnn ROWS nnnnnnnnn
*** KEY xxxxx HAS LOWEST ESTIMATED COST OF nnnnnnnnn
*** RESTRICTION COST =nnnnnnnnn
*** NESTED LOOP COST =nnnnnnnnn
Used to estimate the number of rows that are filtered out. For example, a selectivity factor of 1.0 indicates no rows rejected, 0.5, half the rows rejected, and 0.0 all rows rejected.
The first nn columns of the key are either restricted to a single value, or the last column may be restricted to a range of values.
This type of restriction is called a high-order restriction. The high-order restriction reduces the number of index entries that must be scanned.
Selectivity is based on cardinality statistics. For example, if the first three columns have a high-order restriction and the cardinality at that level is 1000, that is to say, there are 1000 different values within the first three columns of the key, then selectivity is 0.001. This means that when nested-loop is used, each outer table row is estimated to join to only 1 out of every 1000 rows in the inner table.
This is the filtering effect of predicates that can be evaluated from the index, but do not restrict the range of index entries that must be scanned.
The selectivity of each predicate is estimated as one-tenth if the predicate is = else one-third. The total low-order selectivity is the product of the selectivity of each predicate. For example, the low-order selectivity of two = predicates is 1/10 * 1/10 = 1/100.
The process of evaluating low-order predicates is called index scanning.
This is the filtering effect of predicates that must be evaluated from the data record. It is computed the same as low-order selectivity. This process is called data scanning. Since accessing data records is usually more expensive than index entries, data scanning is usually more expensive than index scanning of the same selectivity.
This is the cost of reading the index. It is estimated as the number of index entries to be read divided by the average number of index entries per DXX block, plus the number of levels in the index.
cost = indexEntries / avg per blk + indexLevels
The number of index entries is computed as the number of entries indexed by high-order cardinality. For example, if high-order cardinality is 1,000 and there were 10000 rows indexed at the time the cardinality was computed, the number of index entries is 10000 /1000 = 10.
If OPTIMIZE FOR n ROWS is specified and it is less than the computed value, the number of index entries is reduced to this value.
This is the cost of reading the data area. It is computed by dividing the number of rows to be read by the effective blocking factor of the index.
cost = row / effBlk
The number of data area rows is the number of index entries read times the selectivity of low-order predicates.
The effective blocking factor accounts for the randomness in which the data area is accessed by the index.
effBlk = dataRows * BLKCHG / 1024
where BLKCHG is the average number of times a new data block is encountered per 1024 rows, when accessed in the sequence of this index.
If at least 10 data blocks are to be read and the index is 90 percent in physical sequence, the cost is divided by two, to account for the savings of pre-fetch multi-block reads.
This is the estimated cost of sorting for GROUP BY, ORDER BY, DISTINCT, or UNION. It applies only to the first join. If the first, or outer, table has an index that eliminates the need for a sort, its cost is not included.
This is the estimated number of rows in the result table. It is computed as the number of data area rows times data selectivity.
This is the estimated cost for the index with the lowest estimated cost.
Estimated cost is computed as the sum of the index, data and sort costs, multiplied by the number of rows from the previous join, or for the first join, the outer table restriction estimated rows.
Merge
KEY xxxxx MERGE CANDIDATE FOR TBL authId.tblName correlationName KEY xxxxx SELECTED FROM MULTIPLE CANDIDATES
All equijoin columns are leading columns of the index.
When there are several index merge candidate indexes, this message indicates the index selected. An index may be selected over another candidate for a lower total cost due to its eliminating sorts or a lower index and data scan cost.
SORT MERGE JOIN COSTS: NO JOIN CONDITION - MERGE NOT POSSIBLE GROUP/ORDER/DISTINCT/UNION SORT COST=nnnnnnnnn OUTER TABLE COSTS (SCAN,SORT,READ)=nnnnnnnnn, nnnnnnnnn, nnnnnnnnn INNER TABLE COSTS (SCAN,SORT,READ)=nnnnnnnnn, nnnnnnnnn, nnnnnnnnn
No join condition was specified. Nested-loop method is selected.
Cost of sorting due to GROUP BY, and/or ORDER BY, UNION or DISTINCT.
Restriction cost (cost of reading base table)
Cost of sorting (zero if index-merge)
Cost of reading sorted temporary table (zero if index-merge)
Same as outer table.
TOTAL SORT-MERGE COST =nnnnnnnnn INDEXED MERGE COST USING INDEXES xxxxx AND xxxxx: nnnnnnnnn
Only the message for the method with the lowest estimated cost is given.
Join Step Summary
MANUAL JOIN OPTIMIZATION SPECIFIED - NESTED LOOP USED DISJUNCTIVE JOIN CONDITION - NESTED LOOP USED
Plan option OPT=M is specified, so tables are joined in the order listed in the FROM clause and nested-loop method is used.
Join predicates are under an OR, so merge methods cannot be considered.
*** JOIN STEP LOWEST COST = nnnnnnnn
The lowest estimate of all join methods and indexes.
|
Copyright © 2014 CA.
All rights reserved.
|
|