Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Unanswered: fragment a table


    i have to fragment a table due to reaching the max. possible data pages of 16,777,215 pages.

    the monthly grow of this table is nearly 330,000 pages.
    so i think that it is time to fragment the table.

    what would happen when the max. pages are reached?

    the <oncheck -pT > shows following output:

    Type Pages Empty Semi-Full Full Very-Full
    ---------------- ---------- ---------- ---------- ---------- ----------
    Free 113754
    Bit-Map 3688
    Index 242786
    Data (Home) 14619217
    Total Pages 14979445

    Unused Space Summary

    Unused data slots 0

    Home Data Page Version Summary

    Version Count

    0 (oldest) 14619169
    1 (current) 48

    Index Usage Report for index

    Average Average
    Level Total No. Keys Free Bytes
    ----- -------- -------- ----------
    1 1 69 176
    2 69 56 503
    3 3876 61 356
    4 238840 61 306
    ----- -------- -------- ----------
    Total 242786 61 306

    i want to fragment the table into 3 fragments - one fragment each new dbspace. is it possible or is there an other better way that i can go.

    what size i need for the three new dbspaces? what is the best size of the fragments/dbspace?

    do you have any experiences to fragment a big table
    - duration
    - temp-space
    - etc.


  2. #2
    Join Date
    Jan 2003
    When the limit is hit you will get a cryptic message saying "No more extents".

    It is up to you to figure out how much space you need in each fragment. Do some math to compute how much space in each.

    If you have logging turned on (most likely) you will need tons and tons and tons of log space as ALTER TABLE runs inside a transaction. This is most unfortunate. Especially on huge databases that don't have 40GB of log space. If you can turn logging off it will be good..

    Good luck.

    Also, in 7.3 there were problems with fragmented tables and indexes. At least, my informix had problems. I cannot speak about 9.

Posting Permissions

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