This section of the PXX Report appears only if the Compound Boolean Selection Optimizer performs statistical population counting. Statistical population counting is performed, if more than one candidate index exists after population independent analysis.
This sample report starts counting index candidates SQ157 and SQ158 at level 1 of the index.
*** STATISTICAL POPULATION COUNTING REPORT *** GRP LVL KNAME CNTKR-B LOW EST (TINDX) HIGH EST (TINDX) 0001 0001 SQ157 NOT ENOUGH ENTRIES AT THIS LEVEL 0001 0001 SQ158 NOT ENOUGH ENTRIES AT THIS LEVEL 0001 0000 SQ157 00000002 00000001 NBR ROWS, BLK CHGS 0001 0000 SQ158 0000000A 00000001 NBR ROWS, BLK CHGS 0001 0000 SQ157 00000002 00000002 WINNER HEURISTICS HEADER xxxxx HEURISTICS BUCKETS xxxxx
This portion of the report displays the following information:
GRP - "OR" group number. An "OR" group consists of predicates joined by AND. Statistical population counting is performed separately for each "OR" group.
Displays the Index level counted. Statistical population counting begins with the top level of the index, which is level 1 in this case. If a winner cannot be determined, counting is repeated at the next lower level where a more accurate count can be made.
Displays the name of candidate index. Some indexes may have been eliminated in the population independent phase and not appear here. Also, candidate indexes may be eliminated at a level and not be recounted at lower levels.
Displays the "count key range blocks." This is the number of pointers to lower level blocks counted in the index scan range. The first and last pointer have been eliminated because the portion of entries in these blocks within the scan range is unknown.
NOT ENOUGH ENTRIES AT THIS LEVEL indicates less than three pointers were counted. If more than one index has this message, counting proceeds to the next lower level to obtain a more accurate estimate.
At level 0, this is the actual number of rows counted.
Displays the low and high estimates. The variance of the statistics used for estimation are used to give a low and high estimate. A winning candidate index has a high estimate that is lower than all other candidate's low estimate.
The estimated cost is the sum of the number of index blocks, changes in data area blocks, and temporary index cost. Temporary index cost is computed as one per 10 rows indexed, plus one per 2 rows read from the temporary index, unless retrieval is from the temporary index.
The FST (FOR FIRST) value may limit the number of rows read from the temporary index, or from the permanent index for candidate indexes not requiring a temporary index. It does not limit the rows read from a permanent index while building a temporary index, unless the FOR ANY option is specified.
At level 0, since an exact count has been made there is no range. The number in the LOW EST column is the number of data block changes encountered during the count, except when WINNER or ELIMINATED appears on the right. Then, it is the total estimated cost.
Displays the estimated cost of building a temporary index. If some candidate indexes, but not all candidate indexes, require building a temporary index, this cost is included in the total estimated cost shown in the low/high estimate columns.
When level 0 is counted, the key ID of the winning index is saved. Before counting level 0, a search is made for a heuristic record where the same index has been selected 8 out of the last 10 times that level 0 was counted. If found, that index is used and level 0 is not counted.
The HEADER data identifies the query. The BUCKETS data contains the last 10 winning key IDs.
Heuristic records are only kept for requests that uniquely identify a query in the User Information Block. This is done when the FOR statement is used. If you would like to use heuristics for call-level requests, uniquely identify each query in the first 29 bytes of the User Information Block, and place a valid packed decimal number in the last three bytes. For FOR statements, this number identifies the date of compilation because the query could have changed significantly in a new compilation.
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|