Re: OPTCOMPIND explanation req'd

Peter Whittletion <whittle@ssax.com> wrote in article <5gpauu$qem@cssun.mathcs.emory.edu>... > Am I right in assuming that the OPTCOMPIND parameter is only > relevant to the performance of joins. This is what I thought > it was for, but the ODS 7.2 Performance Guide doc talks about > it in relation to single-table scans. > > Anyone tried it? > > Peter Whittleton (whittle@ssax.com) > > OPTCOMPIND environment variable or configuration parameter also influences single table scan besides join strategy in both 7.1x and 7.2x OnLine versions. If OPTCOMPIND is 0 or 1 with repeatable read isolation level, index scan is preferred. When OPTCOMPIND is 2 or 1 with different isolation level than repeatable read, optimizer compares index scan and full table scan and chooses the best path. The second case can slow down the queries because of optimizer can choose sequential scan inconveniently. Default value of OPTCOMPIND configuration parameter is 2 beginning with 7.10 UD1 (I think)! Regards, Miroslav Lorenc SSA Czech Republic (miroslav.lorenc@ssa.co.uk)