SEO

#1 (**permalink**) 04-20-2008, 07:04 AM

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

#2 (**permalink**) 04-20-2008, 07:05 AM

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/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

slow update table