Re: drop distributions

Hi Art, The ONLY clause in DROP DISTRIBUTIONS is supported in 10.00xC7 and 11.10xC1 and later releases. Regards, Nita Dembla, PMP® IDS Optimizer Development Engineer Information Management IBM Software Group Office: 678-648-2573 (T/L: 599-8675) nita@us.ibm.com "Art S. Kagel (Oninit)" <art@oninit.com> Sent by: informix-list-bounces@iiug.org 03/18/2008 11:21 AM To cc informix-list@iiug.org Subject Re: drop distributions 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 =========================================================================================== _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list