On 5/4/20 2:32 PM, Fehrle, Brian wrote:
Hi all,
This is a shot in the dark in hopes to find a magic bullet to fix an
issue I have, I can’t personally think of any solution myself.
I have a database with hundreds of terabytes of data, where every table
has an integer column referencing a small table. For reasons out of my
control and cannot change, I NEED to update every single row in all
these tables, changing the integer value to a different integer.
Since I have to deal with dead space, I can only do a couple tables at a
time, then do a vacuum full after each one.
Why?
A regular vacuum would mark the space as available.
More below.
Another option is to build a new table with the new values, then drop
the old one and swap in the new, either way is very time consuming.
Initial tests suggest this effort will take several months to complete,
not to mention cause blocking issues on tables being worked on.
Does anyone have any hackery ideas on how to achieve this in less time?
I was looking at possibly converting the integer column type to another
that would present the integer differently, like a hex value, but
everything still ends up requiring all data to be re-written to disk. In
a well designed database (I didn’t design it :) ), I would simply change
the data in the referenced table (200 total rows), however the key being
referenced isn’t just an arbitrary ID, it’s actual ‘data’, and must be
changed.
I'm not following above.
Could you show an example table relationship?
Thanks for any thoughts or ideas,
* Brian F
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
