Paraphrasing the additional details given in additional follow-up
posts:
<pph>
The CPI432C RC5 "AccPth not good for grouping" and CPI432F
"TCER TCSTGE recommended over lib/TAPTC" messages still occur in a
debug joblog even after:
CREATE INDEX lib/TAPTALTC9
ON lib/TAPTC
(TCER, TCSTGE, TCEN, TCDTH)
The query:
SELECT tcer, tcl01, tcl02, tcl03, MIN(tcdth)
FROM taptc
EXCEPTION JOIN taptk
ON tker = tcer
AND tkl01 = tcl01
AND tkl02 = tcl02
AND tkl03 = tcl03
AND tkeffh <= tcdth
AND ( tken = ' '
OR ( tken = tcen
AND :empLevelOK = '1')
)
WHERE tcer = :comp
AND tcstge < 8
AND SUBSTR(tcl03,5,1) = 'S'
GROUP BY tcer, tcl01, tcl02, tcl03
</pph>
First, an advised index may not be used to implement that query.
I have seen the apparent contradiction as a suggestion that using
the index "is deemed too expensive" per RC4, but I understand how
the optimizer might reject the recommended index instead with the
suggestion that the index "is deemed undesirable for grouping", as
effectively synonymous with "too expensive". I have never thought
there was anything wrong, i.e. not truly a contradiction, because I
understood that the advice algorithm does not revise its choice for
recommendation based on the existence of an access path with the
matching keys; matching keys will still be rejected for [the] other
reasons such as undesirable pagesize, temporary vs live data
requirements, memory limitations, index maintenance (rc1 & rc2), etc..
Second, why are the additional columns named in the CREATE INDEX
when the algorithm determined that the optimizer wanted only the two
columns it named? If indeed only the two columns were mentioned,
DROP INDEX of the one created above, and CREATE INDEX to create the
access path with only the two keys. Same result, or different? If
the same result, what is the selectivity of the ANDed conditions in
the WHERE clause, the likely selectivity of the first test in the
WHERE clause, and can the additional logic involving column TCEN be
moved to the WHERE clause?
Third, using the debug messages is discouraged. As I recall they
were going to be updated only minimally, having been replaced by the
database feature of iNav. The Visual Explain is the recommended
method to review query implementations, and the Index Adviser is the
recommended replacement for the function of CPI432F.
Regards, Chuck
Dennis Lovelady wrote:
I have a somewhat complex query that resulted in CPI432F
(suggested access path, essentially). This query joins two
files, and the suggestion is to add an index over the first file
(TAPTC) using keys of TCER and TCSTGE.
I then created a DDS-based logical over the TAPTC file using the
suggested keys since there are other applications that can take
advantage of this. Then I ran my query again.
Oddly, the same message appears in the joblog (with debug
active), and the same suggestion of TCER and TCSTGE. Is this an
expected behavior at V5R3? I don't think I've run into it before.
(Note: I did ensure that the file I created actually exists, and
that it's over the right table, and so on. In fact, it is listed
as the second "access path considered" in CPI432C message.)
midrange.com
