Q
Problem solve Get help with specific problems with your technologies, process and projects.

Working with query optimizer

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.

SQL statement:
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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close