alter index to cluster

alter index to cluster

Post 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?
 
 
 

alter index to cluster

Post 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?


 
 
 

alter index to cluster

Post 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?

 
 
 

alter index to cluster

Post 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

 
 
 

alter index to cluster

Post 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

 
 
 

1. Alter index to cluster?

Is there a way to alter the index to cluster?
To reset the values of the primary key ?
I tried deleting the ID column and then recreating it.
But the order of the records remains the same. It follows some
internal index which is really the one I want changed.

I now  have:
ID      NUM
1       15      
2       14      
3       11  <<<<<<
4       13
5       12

and would like:
1       15
2       14
3       13
4       12
5       11 <<<<<

loudelon...

2. LOOKING FOR: An address of UniFace in The Netherlands (Holland)

3. Should we recommend alter index to cluster?

4. ADO & VB

5. Alter index to cluster

6. Pattern Matching

7. Attempting to ALTER INDEX TO CLUSTER on Primary Key

8. general information?!

9. Alter index to cluster

10. ALTER (Constraint)-INDEX TO CLUSTER, why not possible ?

11. clustered index vs non clustered index

12. Alter fragment/Alter Index

13. create clustered / non-clustered index