Unanswered: Does an index rebuild after a load need less temp space than index creation?
I am having a problem to create an index.
It is on a huge table of several billion records.
Each record contains just 3 small values:an integer, a char(11) and a bigint.
But when I try to create an index on the char field,
DB2 starts to use a huge amount of temporary space: more than 400 Gb.
And then I had to cancel the index creation because the file system was almost full.
The file system cannot be extended.
So I thought to try it this way, but I am not sure if that will need less temporary space:
What if I:
1. Export the table.
2. Drop it.
3. Re-create it WITH the index.
4. And then reload the data.
(The table can be offline for a long time as it is only used in the beginning of each month.)
Does anyone know if this will need less temp space?
Or will I run into the same problem when the load starts to rebuild the index?
I prefer to ask it here, because this operation could well take a day or more.
And if that does not solve my problem, I will have wasted much time for nothing.
So, does anyone have any experience with comparing index creation vs index rebuild,
and if one of them needs less temp space?