OPTCOMPIND

All, This may seem like a silly question, but what would be a good reason to ever set OPTCOMPIND to anything but 2? If 0 and 1 each bind the optimizers options to various degrees, what could possibly be the advantage of that? I'm curious. Any real life experiences where 0 or 1 was a better choice? Kind regards, -- <><><><><><><> John Bejarano San Mateo, CA <><><><><><><> --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---

Hi, years ago some people set OPTCOMPIND to 0 to ensure that the optimizer will use their index. I think most of the Informix customers knew that the OPTCOMPIND parameter didn't only influence the behaviour of table joins, but also the classic single-table lookup :-) Starting with version 4.1 the Informix optimizer was able to avoid an index. But starting with version 7.1 they needed a way to avoid a dynamic hash join. By setting OPTCOMPIND to 0 you had the chance to force the optimizer to do whatever you wanted. And there's a reason to set the environment variable OPTCOMPIND to 1, if you temporarily use the isolation level "repeatable read". By setting OPTCOMPIND = 1 the optimizer will prefer the index lookup while your application uses the RR level, otherwise dynamic hash joins. The problem with the RR level is, that all processed rows ( and that is more than the resulting rows ) are locked until the end of the transaction. Very often a dynamic hash join will cause a full table scan on the larger table. OPTCOMPIND = 2 would end in a full table lock until the end of the dynamic hash join. Just a few environments, where ( I hope ) most of the customers use OPTCOMPIND = 0, are SAP/R3 and BaaN. BaaN will start to use OPTCOMPIND=2 with their new level/2 driver. Best regards, Stefan Weideneder John Bejarano wrote: > > All, > > This may seem like a silly question, but what would be a good reason to > ever set OPTCOMPIND to anything but 2? If 0 and 1 each bind the > optimizers options to various degrees, what could possibly be the > advantage of that? I'm curious. Any real life experiences where 0 or 1 > was a better choice? > > Kind regards, > > -- > <><><><><><><> > John Bejarano > San Mateo, CA > <><><><><><><> > > --== Sent via Deja.com http://www.deja.com/ ==-- > ---Share what you know. Learn what you don't.--- -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----