Hi All,
Need to fragment the table for performance reason. What should I do with the index. Should I detach it first then followed by table fragment? or may be any other ways to do it ?
How could I do reindex the table after the table was fragmented?
Thank you very much.

Hi,

I suggest that save the schema of the table, next drop all index, next run ALTER FRAGMENT ON TABLE xxxxx INIT IN FRAGMENT BY [expression | round robin] IN dbspace list. Next create index. This indexes will be fragmented as table.

Gustavo.

Hi Gustavo,
Q1- How to estimate the dbspace list ? How big for each dbspace should be ? should I base on the table size and and divide by the dbspace list and allocate the size accordingly or few Gb extra ?

Q2- Is the table size is still limit to 64 Gb once the table was fragmented ??

Q2: The table limit will be aprox 16MillionPages per fragment.

Q1: It depends. I usually recomend to create 1 fragment per different 'logical' disk - if you have a disk cabinet, RAID or someting like it makes 1 big disk -> create all the dbspaces you like.
The extent sizes you use are the fragment extent sizes:
extent size 100 next size 50: every fragment will be 100Kb when created.

About the dbspaces size: divide and take an extra Gb - say 2x36, or 4x20, or 8x10... but take care about the fragment expressions.

BTW: it would be nice to have the table schema (use the -ss option for dbschema) and know which columns your apps use most.

It is not the 64 GB table limit that you've reached but the 16,777,715 pages per table fragment limit. When you fragment your database, each dbspace is a fragment, hence 2 dbspaces = 2 fragments, and total pages will be 2 * 16,777,715 pages.

On you Q1: I'm sure you can figure that out.