Home > IT Answers > Database > Is it possible on iSeries that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used.
JohnsonMumbai
1,160 pts.
 Is it possible on iSeries that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used.
Index Performance, iSeries, SQL, SQL Select, V5R4
Is it possible on i series that an index is defined keeping in mind an sql select criteriea however when the queryis run this index does not get used. We had an issue wherein an entire library had to be cleared and all files from had to be restored from a previous backup, after the backup was restored we found one of the sql queries taking much more time than before the restore , although all files and its logicals where restored.

 We ran dbmon on the sql job, and it suggested creation of few indexes which we created using names specified by us and using the recommeded fields. However when the sql was submitted we did not find any of the indexes created by us being used. Why is that?



Software/Hardware used:
os400, v5r4, i570
ASKED: December 23, 2010  9:45 AM
UPDATED: December 24, 2010  2:41 AM
RELATED QUESTIONS
Answer Wiki:
Hi JohnsonMumbai: I do not know for sure since I had asked that question myself, but I think you should use the new index (which I assume is a logical file or view) in your query. Then you can be sure it is used (specially if the query order matched the logical). FlorenJM
Last Wiki Answer Submitted:  December 23, 2010  7:17 pm  by  Florenjm   105 pts.
All Answer Wiki Contributors:  Florenjm   105 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

JohnsonMumbai -
IBM has what I would call a Query Analyser. If you are able to, run the job in Debug mode with full job loging. It should tell you why it took the path that it did. I do this all the time with RPG/SQL programs. It can make a HUGE difference.

Phil

MurrayInfoSys
 940 pts.
 

Yes, it is possible. It is even likely.

Most indexes are not used in most queries. You might have ten indexes over a table, but usually no more than one is used; and sometimes none are used. You might have an index over a column and reference the column in a WHERE or ORDER BY clause, and the index still might not be used.

To find out why an index is used or not used, follow Phil’s advice. Run the query in debug. SQL will log messages about the choices it makes when debug is active.

iSeries Navigator also has a number of options that can help analyze how queries are run. Drill down into Databases and right-click on your system’s database name. Select ‘Run SQL Scripts…’. Review the menu options to see what’s available. Pay special attention to the VisualExplain menu item.

Tom

TomLiotta
 108,360 pts.