drop distributions

Hi. After upgrading from IDS 9.40.FC4W2 to 9.40.FC9W2 some queries run extremly slow and I'm under the impression that the optimizer chooses the wrong way especially when OPTCOMPIND is 1 or 2. My idea is to drop distributions. The problem is "update statistics low on table xxx drop distributions" runs very, very long on large tables. So, is it safe, to delete the rows for the table in the sysdistrib table? Update statistics low could be run anyway. The advantage is that it would cost no time to test the optimizer without distributions. BTW: We have an OLTP and Decison Support mixed environment with very large tables. What do you think is the best value for OPTCOMPIND in onconfig. In 9.40 is not possible to "set OPTCOMPIND" with the session and we have applications that cannot set an environment variable. And is there any advantage of distributions if I set OPTCOMPIND to 0? Thanks in advance for your help. Reinhard.

\

Habichtsberg, Reinhard wrote: > Hi. > > After upgrading from IDS 9.40.FC4W2 to 9.40.FC9W2 some queries run extremly > slow and I'm under the impression that the optimizer chooses the wrong way > especially when OPTCOMPIND is 1 or 2. > > My idea is to drop distributions. The problem is "update statistics low on > table xxx drop distributions" runs very, very long on large tables. So, is > it safe, to delete the rows for the table in the sysdistrib table? Update > statistics low could be run anyway. The advantage is that it would cost no > time to test the optimizer without distributions. > Quickest way to drop distributions, if your version supports it, is to use new syntax: UPDATE STATISTICS FOR TABLE tabname DROP DISTRIBUTIONS ONLY; I've gotten away with the DELETE FROM sysdistrib option where the above syntax is not supported, but support tends to frown on this. BTW, I have a similar problem with the IDS 10 family, where having distributions causes the optimizer to choose the wrong path. But in my case, OPTCOMPIND seems to be irrelevant, and I've replicated on IDS 10.00.FC6 and IDS 10.00.FC8

tgirsch wrote: The DROP DISTRIBUTIONS clause with the ONLY option is not available in 9.xx. Don't remember if it was added in 10.00 or 11.10, but it's not in 9.40. Art S. Kagel Oninit > Habichtsberg, Reinhard wrote: > >> Hi. >> >> After upgrading from IDS 9.40.FC4W2 to 9.40.FC9W2 some queries run extremly >> slow and I'm under the impression that the optimizer chooses the wrong way >> especially when OPTCOMPIND is 1 or 2. >> >> My idea is to drop distributions. The problem is "update statistics low on >> table xxx drop distributions" runs very, very long on large tables. So, is >> it safe, to delete the rows for the table in the sysdistrib table? Update >> statistics low could be run anyway. The advantage is that it would cost no >> time to test the optimizer without distributions. >> >> > Quickest way to drop distributions, if your version supports it, is to > use new syntax: > > UPDATE STATISTICS FOR TABLE tabname DROP DISTRIBUTIONS ONLY;> > I've gotten away with the DELETE FROM sysdistrib option where the above > syntax is not supported, but support tends to frown on this. > > BTW, I have a similar problem with the IDS 10 family, where having > distributions causes the optimizer to choose the wrong path. But in my > case, OPTCOMPIND seems to be irrelevant, and I've replicated on IDS > 10.00.FC6 and IDS 10.00.FC8 > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Habichtsberg, Reinhard wrote: > Hi. > > After upgrading from IDS 9.40.FC4W2 to 9.40.FC9W2 some queries run extremly > slow and I'm under the impression that the optimizer chooses the wrong way > especially when OPTCOMPIND is 1 or 2. > > My idea is to drop distributions. The problem is "update statistics low on > table xxx drop distributions" runs very, very long on large tables. So, is > it safe, to delete the rows for the table in the sysdistrib table? Update > statistics low could be run anyway. The advantage is that it would cost no > time to test the optimizer without distributions. > > BTW: We have an OLTP and Decison Support mixed environment with very large > tables. What do you think is the best value for OPTCOMPIND in onconfig. In > 9.40 is not possible to "set OPTCOMPIND" with the session and we have > applications that cannot set an environment variable. > > And is there any advantage of distributions if I set OPTCOMPIND to 0? > > Thanks in advance for your help. > > Reinhard. If you can afford the testing, do "UPDATE STATISTICS FOR TABLE tabname(xpto) DROP DISTRIBUTIONS" where "xpto" is a column that *doesn't* belong to any index... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Art S. Kagel (Oninit) wrote: > tgirsch wrote: > > The DROP DISTRIBUTIONS clause with the ONLY option is not available in > 9.xx. Don't remember if it was added in 10.00 or 11.10, but it's not in > 9.40. > They've been sneaking it in as a backport in UCx versions. It's not supported in 10.00.FC6, but is supported in 10.00.FC8; not sure if they introduced it with some UCx version of 9.40, but they may have.