table extents

#1

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

#2

Rüdiger Mähl wrote:

Quote:

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

#3

"Rüdiger Mähl" <ruediger.maehl (AT) web (DOT) de> wrote:

Thanks a lot.

Rüdiger

#4

"Gorazd Hribar Rajteriè" <gorazd.hribar (AT) telekom (DOT) si> wrote

Quote:

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).

Well, on any platform.
"Fixed" in IDS 9.40.
A few other things to bear in mind: if the target dbspace is itself
fragmented (ie lots of small blocks of free space) the ALTER FRAGMENT can
fail through exceeding the maximum number of extents for a tablespace.
Also, on large tables this is best done with database logging off for
performance and for avoidance of long transactions.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

table extents

comp.databases.informix comp.databases.informix

table extents - 09-15-2003 , 08:52 AM

Re: table extents - 09-15-2003 , 12:37 PM

Re: table extents - 09-16-2003 , 03:29 AM

Re: table extents - 09-17-2003 , 10:06 AM