Re: Extent size

Albert Wisse wrote: > > -- Hello, > > I have a database with many tables, a few thousand tables and due the > behavior of the product I can`t set the initial extent size so that the > amount of extents is minimal. > > Why can't I do the following; "with alter index ?? to cluster" I > recreate the table in the order given by the index so why not having the > possibility to reset the initial extent size to ("size of the > table"/n)+("initial extent size"/p) and the next extent size to > ("initial extent size/p"). > The n and p parameters are extra options to "alter index ?? to cluster" > command. > > This gives my the possibility to rebuild the database in the weekend, > automate it. You cannot change the initial extent size but you can change the next extent size to contain the entire table except the initial extent and if you are lucky the two extents will be contiguous and the engine will compress them into one extent. However, use the ALTER FRAGMENT syntax to reorganize the table it is far faster than ANTER INDEX...TO CLUSTER since no sorting is required. Since ALTER FRAGMENT copies data pages first followed by index entries, and since the index entries need to be rewritten to change the rowid's the ALTER FRAGMENT syntax compresses the indexes also making them contiguous and reducing the number of leaves and levels where possible. The syntax would be: ALTER TABLE mytable MODIFY NEXT SIZE 12345; -- Where 12345 is the number of pages in the table. ALTER FRAGMENT ON mytable INIT IN dbspace; -- dbspace can be any dbspace, even the same one the table is already in! Art S. Kagel