Re: Optimizing UPDATE on SE

Rajendra Singh wrote: > I have a simple SQL statement: > UPDATE tablename SET textcolumn1 = "a" WHERE textcolumn2 <> "b"> There is an index on "textcolumn2". > > On an (SE) table with approximately 106000 rows (on HP-UX 10.20) > it takes about 40 minutes to execute the above statement. Is there > a way that I can speed up the execution of this statement > significantly? Drop any indexes on textcolumn1? Increase the speed of your disks? The index won't be used because the not equals condition is very unselective and a sequential scan is necessary (unless the distributions of the values are heavily skewed, but then SE doesn't have or use distributions, either). Consequently, you are limited to how long it takes to read and rewrite the table. If there are any indexes on textcolumn1, they will be being updated (with very repetitious values), which is not a good idea. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>