Very Computer

by **Top Ca** » Sun, 31 Dec 1899 09:00:00

I had a table which had 22 extents. I altered the unique index to cluster
through isql. Now, oncheck -pT reports that the table has 20 extents.
Shouldn't it be just 1? When I do a dbschema on the table the index is
listed as 'unique cluster index,' so why are there 20 extents?

by **Chris Jenkin** » Sun, 31 Dec 1899 09:00:00

When the 'alter table' re-built the table there wasn't enough
contiguous free space to take the table as one extent!

>I had a table which had 22 extents. I altered the unique index to cluster
>through isql. Now, oncheck -pT reports that the table has 20 extents.
>Shouldn't it be just 1? When I do a dbschema on the table the index is
>listed as 'unique cluster index,' so why are there 20 extents?

by **Lucie Janstov** » Sun, 31 Dec 1899 09:00:00

Your idea is bad. Cluster's index is not resolve count of extents.
Problem is definition table. Try it:

1) dbschema -d my_dtb -t my_table -ss > my_script.sql
2) unload to "file.unl" select * from my_table
3) drop my_table
4) modify my_script.sql ( you must modified value first and next extent ).
5) create my_table with clausule first, next extent ( in file
my_script.sql ).
6) load from.... insert into ...

You must calculated value "first extents" from count all extents. Table
systables
contain actual value first and next extent. Attention !! Informix engine
modify
value "next extent" with every next allocate ( when last extent is full ).
You set value "next extent" as 10% first extent.

Cluster's index is used only for static table ( table which is not modified
or table is modified the least ). Sentences are sorted physical by index's
columns.

Lucy

>I had a table which had 22 extents. I altered the unique index to cluster
>through isql. Now, oncheck -pT reports that the table has 20 extents.
>Shouldn't it be just 1? When I do a dbschema on the table the index is
>listed as 'unique cluster index,' so why are there 20 extents?

by **Neil Trub** » Sun, 31 Dec 1899 09:00:00

>Your idea is bad. Cluster's index is not resolve count of extents.
>Problem is definition table. Try it:

>1) dbschema -d my_dtb -t my_table -ss > my_script.sql
>2) unload to "file.unl" select * from my_table
>3) drop my_table
>4) modify my_script.sql ( you must modified value first and next extent ).
>5) create my_table with clausule first, next extent ( in file
>my_script.sql ).
>6) load from.... insert into ...

A faster, more elegant and less stressful way is to use ALTER FRAGMENT ....
INIT. This allows you o modify the NEXT EXTENT size (but not the first
extent).

Neil Truby
Londis Stores
Hampton Hill, UK

by **Rock, Howar** » Sun, 31 Dec 1899 09:00:00

----------

Sent: Tuesday, August 03, 1999 9:19 AM

Subject: alter index to cluster

I had a table which had 22 extents. I altered the unique index to
cluster
through isql. Now, oncheck -pT reports that the table has 20
extents.
Shouldn't it be just 1? When I do a dbschema on the table the index
is
listed as 'unique cluster index,' so why are there 20 extents?

There might not be enough contiguous space for just 1 extent. If you
cluster an index there needs to be enough contiguous space for the entire
table for the table to be truly de-fragmented