Re: drop distributions

Hi Reinhard, It is faster to specify individual column names when dropping distributions, as in: update statistics low for table TABLE(COLUMN); An example could be something similar to the following.... ( -- is a comment ) #------------------------------------------------------------------------ DBNAME=somedb TABNAME=sometb echo " select a.tabname, b.colname from systables a, syscolumns b, sysdistrib c where a.tabname=\\"$TABNAME\\" and a.tabtype='T' and a.tabid=b.tabid andb.tabid=c.tabid and b.colno=c.colno and c.seqno=1; " | dbaccess $DBNAME 2>/dev/null | egrep -v "^$|^tabname .*colname$" | while read tnm cnm do echo "-- update statistics low for table $tnm($cnm) drop distributions;" done | dbaccess -e $DBNAME 2>/dev/null #------------------------------------------------------------------------ HTH, Tim "Habichtsberg, Reinhard" <RHabichtsberg@arz-emmendingen.de> Sent by: informix-list-bounces@iiug.org 03/18/2008 03:07 AM To "Informix-List (E-Mail)" <informix-list@iiug.org> cc Subject 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. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list This message may contain confidential and/or privileged information. This information is intended to be read only by the individual or entity to whom it is addressed. If you are not the intended recipient, you are on notice that any review, disclosure, copying, distribution or use of the contents of this message is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete or destroy any copy of this message.