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 BEFORE
followingcolumn; -- 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/