Re: Shared Memory allocation error

In message <8525647D.0065C7E6.00@gty02.homedepot.com>, Kate_Tomchik@HomeDepot.COM writes } } } } }Why does setting OPTCOMIND to 2 use more memory? } } OPTCOMPIND = 2 lets the optimizer deciede the best query path by cost alone and these costs are often wrong. It tends to do sequentail scans and hash joins on tables. This means it allocates memory for the has table in the virtual portion of shared memory. This hash table will be at last the size of the join key in the smaller table e.g. 20Mb! Setting OPTCOMPIND=0 means it will use indexes instead when they are # available. This uses nested loop joins hence only requiring one copy of the join key in memory for the number of rows in the result set NOT the whole table. Hence OLTP queries which typically only return a small percentage of the rows in a table should have OPTCOMPIND=0. } }Please respond to djw@smooth1.demon.co.uk } }To: informix-list @ rmy.emory.edu }cc: (bcc: Kate Tomchik/IS/SSC/THD) }Subject: Re: Shared Memory allocation error } }> }> } } Make sure OPTCOMPIND = 0 (NOT 2)! This should reduce memory } requirements considerably. } } } }-- } } } -- David Williams