slow update table

Hello, We are running the following query (with no where clause): update tablename set columnname = 'BA' The table has about 300,000 rows and the update is taking days. We do not have an index on this column. Can anyone offer suggestions as what we should do? The system is Informix Dynamic Server 9.3 running on an E3000 with Solaris 8. Thanks, Steve Kurlander

Steven Kurlander wrote: > We are running the following query (with no where clause): > > update tablename set columnname = 'BA'> > The table has about 300,000 rows and the update is taking days. We do > not have an index on this column. Can anyone offer suggestions as > what we should do? > > The system is Informix Dynamic Server 9.3 running on an E3000 with > Solaris 8. How big is each row in the table? This is not tested, but... ALTER TABLE DROP columnname; ALTER TABLE ADD columnname <type> DEFAULT 'BA' NOT NULL BEFOREfollowingcolumn; -- check syntax! That should be two in-place alters (fast) - and would achieve the required result. *Maybe* It is not tested! More seriously, why is the original update taking so long - as in days. And I don't have a good answer for that. What triggers are on the table? What else could account for the operation going so slow? Is it a VTI table of some sort? On a 'normal' table - say a hundred or so bytes wide - that should not take even hours, let alone days. So, there's something abnormal about the table? The absence of an index on the column should be irrelevant - it cries out for a table scan regardless. And the index on the column would be the ultimate in heavily duplicate indexes - not a good idea. That chunk isn't on a floppy disk, is it? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/