Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

MS SQL dev here, this is my first attempt to optimize a DB2 query on iSeries. I was asked to help improve a huge query which consists entirely of dozens of joined subselects. Some of those subselects include unions and where exists/not exists clauses were used extensively. Among other things there are many where clauses that specify column <> 0 etc. Does iSeries DB2 efficiently use index with where column <> 0, <> '' etc?

share|improve this question
    
What do you mean by "efficiently using" in this case? –  mustaccio Oct 28 '13 at 20:20
    
Ex: In MS SQL <> may cause optimizer not use an index a column is a part of. –  ajeh Oct 28 '13 at 20:35
1  
There are a lot of things about your description that may cause an optimizer not use an index, particularly if the column is not the leading one in the index, so your question as stated does not have an answer I'm afraid. Or, as other folks often respond, "it depends". –  mustaccio Oct 28 '13 at 21:09
1  
I've been taught (specifically as it relates to DB2-more LUW though- and I would assume is also good advice for other RDBMS's) that one should always ask the database engine for what you want, not for what you don't want. The more you can get specific with what you want, the more the optimizer can use to obtain your results. –  Chris Aldrich Oct 29 '13 at 13:16

1 Answer 1

Using System i Navigator, you can run your queries in there to see if there are more indexes that may be recommended. The system tries it best to create the right indexes before running and caches those access paths.

One thing I did in my last project with some larger queries was to create procedures. Then the system stores those access paths and speeds up your queries even more.

share|improve this answer
    
Along those lines, deterministic functions can sometimes help as well. –  WarrenT Oct 30 '13 at 0:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.