Optcompind

I still consistently see the suggestion on the list that optcompind should be set to 0 rather than 2. I've run with 2 for quite a long time and I saw a suggestion today that said to change it to 2 rather than 0. Is that something that was "fixed" in a later version (the question was in regards to IDS 2000)? In other words, are there versions that you should never set it to 2 on but should always use 0? Or is this just another case of try it and see what works best for you type of thing?

"Weaver, Bill" wrote: > > I still consistently see the suggestion on the list that optcompind should > be set to 0 rather than 2. I've run with 2 for quite a long time and I saw > a suggestion today that said to change it to 2 rather than 0. Is that > something that was "fixed" in a later version (the question was in regards > to IDS 2000)? In other words, are there versions that you should never set > it to 2 on but should always use 0? Or is this just another case of try it > and see what works best for you type of thing? In general, for an OLTP instance OPTCOMPIND should be set to 0 so that index use and nested loop joins are favored heavily over hash-joins because the latter will delay interactive responsiveness needed on an OLTP system. In general, for a DSS/DW instance OPTCOMPIND should be set to 2 so that the optimizer is free to choose the cheapest overall course of action. The suggestion I made yesterday was a situation where the questioner felt that a nested loop join was too slow and that an earlier version was using hash joins. This would indicate that in the upgrade he had changed the value of OPTCOMPIND from 2 to 0 and was not happy. Looks like a DSS server to me so I suggested the change. When someone complains of responsiveness I can assume an OLTP or OLTP-like environment (what I called a simple-query environment in my TechNotes article) and recommend OPTCOMPIND 0. No inconsistency just different response for different situations. Art S. Kagel