Why is the optimizer not selecting an index where the first two keys from my primary file match the first two keys of my secondary file? I get reason codes 4 and 17. Reason code 17 is easy to understand, but I cannot understand why reason code 4 is selected for my existing index.
SELECT COUNT(*) FROM RIMVSIPF A EXCEPTION JOIN IMMWIMPF B ON A.SRSTN=B.WISTN AND A.SRUPC=B.WIUPC
LF Indexes I want it to use:
RIMVSIL7 -SRSTN, SRUPC, SRVND IMMWIML2 -WISTN, WIUPC, WIEDAT
All of the matching fields are the same type and length. Why does the optimizer decide that RIMVSIL7 is not good enough to use? The system says it will take approximately 1580 seconds to process records of 2,755,088 for RIMVSIPF and 18,813,250 for IMMWIMPF.
I have created other index combinations trying to reduce the time frame with some success, but I would rather the system use an existing index that has starting positions that match the other index's starting positions.
Hard to say without knowing how the statement is being implemented currently. My best guess would be since there is no local selection (WHERE col1=100) RIVMSIPF that the query optimizer is performing a table scan instead of using an index. If you have to read all of the rows in the table, then it's always faster to use a table scan vs. an index scan.
MORE INFORMATION ON THIS TOPIC
Ask your systems management questions--or help out your peers by answering them--in our live discussion forums.
The Best Web Links: Tips, tutorials and more.
Check out this Search400.com Featured Topic: Networking and the iSeries
PC/Windows connectivity expert Shahar Mor is available to answer your toughest networking questions. Ask Shahar a question or read his previously answered questions here.
Dig Deeper on FTP
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.