| Ian Burns 2005-03-30, 7:08 pm |
| I have a somewhat similar situation, and I don't believe the
advice
below is appropriate. I have a table with ~42,000,000
rows, and
need to update 4 fields in ~5,500,000 rows of the table to
NULL. I
am driving the update using a table containing ~656,000
unique
keys, with an HG index, that corresponds to a field in the
target
table, also having an HG index.
The update then is:
update target
set field1 = NULL, field2 = NULL, field3 = NULL, field4 =
NULL
from target t, keylist k
where k.key = t.key;
This has been running for > 1 day, and has still not
completed. My
concern with dropping indices is that I need to update only
a
subset of all rows in the table, and I assume dropping them
will
negatively impact performance, or that the total cost in
time of
dropping the indices and recreating them after the update
will negate any benefit. Am I right?
> This reply might be a bit late, but have you considered
> just dropping the HG indexes before you set the columns to
> null? Then re-create them prior to the load.
>
> On 20 Dec 2004 16:37:25 -0800, garretts wrote:
>
> we >update periodically using a partial-width insert. In
> order >to issue the partial-width insert, we first have to
> update >the columns to contain all NULL values. That
> update, >however, is taking quite a long time--more time,
> in fact, >than the partial-width insert itself!
> , >col_c = null". This update takes 50 minutes.
> ..". >This takes about 15 minutes.
> better >performance if I dropped and recreated the columns
> with >"alter table" statements, but that seems silly and
>
|