Allocating Table Space

I have a 3.5 million row table with indexes attached. I want to defragment the table and detach the indexes moving them to their own dbspace on another disk. ONCHECK pT shows that there are currently 50000 data pages and almost 200000 index pages. I plan to unload the table, drop the table, recreate the table, reload the data and recreate the indexes. Since I am going to put the indexes in their own dbspace how large do I set the EXTENT SIZE in the CREATE TABLE statement? Is it set to the equivalent of 50000 pages and then let the indexes fare for themselves or is it set to 250000 pages and the space is somehow shared between the data and index dbspaces? Thanks for your help.

In article <378624C0.CB585A56@wsu.edu>, Dale Forrey <forrey@wsu.edu> wrote: > I have a 3.5 million row table with indexes attached. I want to > defragment the table and detach the indexes moving them to their own > dbspace on another disk. ONCHECK pT shows that there are currently > 50000 data pages and almost 200000 index pages. I plan to unload the > table, drop the table, recreate the table, reload the data and recreate > the indexes. What about ALTER FRAGMENT ON [idx_name or tab_name] INIT IN dbsname ? This can be MUCH faster, but you must take care about logging, and if you have triggers you must recreate them. Or, drop all indexes but one, and use for this index ALTER INDEX TO CLUSTER. This will recreate your table with no extent interleaving. Then mofify NEXT SIZE and recreate other indexes as desired. > Since I am going to put the indexes in their own dbspace > how large do I set the EXTENT SIZE in the CREATE TABLE statement? Is >it > set to the equivalent of 50000 pages Yes. >and then let the indexes fare for > themselves Yes. > or is it set to 250000 pages and the space is somehow shared > between the data and index dbspaces? No. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.