Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011

    Unanswered: Does an index rebuild after a load need less temp space than index creation?

    Hello people;

    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?

    Thank you in advance!

  2. #2
    Join Date
    Jul 2013
    Moscow, Russia
    Provided Answers: 55

    Load keeps all keys in memory. So, creating indexes after the load operation should use less memory if you have multiple indexes.
    You can try to partition your table and use local indexes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts