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?
1 Answer
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.
-
Along those lines, deterministic functions can sometimes help as well.– WarrenTOct 30, 2013 at 0:29
<>
may cause optimizer not use an index a column is a part of.