table extents

Alarmed by the extent discussion some days ago, I checked the number of extents of all "my" tables in all databases. The result was terrifying: there are a lot of tables with over 20 extents and even some tables with more than 100 extents. So, what can I do to get rid of fragmentation? I know of dbexport and dbimport. How about: ALTER INDEX <idx> TO CLUSTER and back again? Any other smart tricks? TIA, R'diger

R'diger M'hl wrote: > Alarmed by the extent discussion some days ago, > I checked the number of extents of all "my" > tables in all databases. The result was terrifying: > there are a lot of tables with over 20 extents > and even some tables with more than 100 extents. > > So, what can I do to get rid of fragmentation? > I know of dbexport and dbimport. How about: > ALTER INDEX <idx> TO CLUSTER > and back again? ALTER FRAGMENT? -- "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche

Use: ALTER TABLE <table name> INIT IN <dbspace name> you can use the same dbspace, where your table is created originally. The whole table will be rebuilt and number of extents will be minimized. You can use: ALTER TABLE <table name> MODIFY NEXT SIZE <new number> prior running ALTER FRAGMENT clause to ensure sane values for next extent sizes. Note that on IDS 7.31 and prior values over 2 GB have no effect (at least on Sun machines). Check for full syntax on http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/sqls/01alloc.fm.html#131549 Gorazd "R'diger M'hl" <ruediger.maehl@web.de> wrote in message news:Xns93F7A17645FC9ruedigermaehlwebde@193.101.67.2... > Alarmed by the extent discussion some days ago, > I checked the number of extents of all "my" > tables in all databases. The result was terrifying: > there are a lot of tables with over 20 extents > and even some tables with more than 100 extents. > > So, what can I do to get rid of fragmentation? > I know of dbexport and dbimport. How about: > ALTER INDEX <idx> TO CLUSTER > and back again? > > Any other smart tricks? > > TIA, > > R'diger

Addition to previous post: If your tables are big, you may have problems with logs. I would recommend switching database to non-logging mode before running ALTER TABLE and ALTER FRAGMENT statements and then back again. That is of course if you can afford down time for your users... Gorazd "Gorazd Hribar Rajteri'" <gorazd.hribar@telekom.si> wrote in message news:RCy9b.2850$2B6.621071@news.siol.net... > Use: > ALTER TABLE <table name> INIT IN <dbspace name> > you can use the same dbspace, where your table is created originally. The > whole table will be rebuilt and number of extents will be minimized. You can > use: > ALTER TABLE <table name> MODIFY NEXT SIZE <new number> > prior running ALTER FRAGMENT clause to ensure sane values for next extent > sizes. Note that on IDS 7.31 and prior values over 2 GB have no effect (at > least on Sun machines). > Check for full syntax on > http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/sqls/01alloc.fm.html#131549 > > Gorazd > > "R'diger M'hl" <ruediger.maehl@web.de> wrote in message > news:Xns93F7A17645FC9ruedigermaehlwebde@193.101.67.2... > > Alarmed by the extent discussion some days ago, > > I checked the number of extents of all "my" > > tables in all databases. The result was terrifying: > > there are a lot of tables with over 20 extents > > and even some tables with more than 100 extents. > > > > So, what can I do to get rid of fragmentation? > > I know of dbexport and dbimport. How about: > > ALTER INDEX <idx> TO CLUSTER > > and back again? > > > > Any other smart tricks? > > > > TIA, > > > > R'diger > >

"R'diger M'hl" <ruediger.maehl@web.de> wrote: Thanks a lot. R'diger