MVSFORUMS.com

[showkath]

Hi,

I am looking for the exact syntax or samples for altering a clustered index for redefining the index cluster with new values.

Also please let me know the steps involved in implementing this change.

I think, after the alter index just do a re-org. Am I correct ? Please help.
_________________
Regards,
Showkath
-----------------------------------------------------
Our opinions do not really blossom into fruition until we have expressed them to someone else - Mark Twain

[videlord]

eg. ix1 is the old cluster index, ix2 will be used as new cluster index

DDL:

ALTER INDEX ix1 NOT CLUSTER;
ALTER INDEX ix2 CLUSTER;

after alter index, REORG will help performance improvement

[SMS]

Thanks.

I already tried like the below one. But It's throwing error.

ALTER INDEX <index name> CLUSTER
(PART 1 VALUES(xxxx),
PART 2 VALUES(xxxX)
...
PART 10 VALUES(xxxx));


Actually the table space is partitioned. Index space is partitioned based on 10 values. Now the 10 values needs to be re-arranged.
_________________
Regards,
SMS

[videlord]

What's the error message?

Maybe you should try to use 2 ALTER statements, one for ALTER cluster, and the other for ALTER partition key range.

[SMS]

I got it. Actually the parenthesis is not required after CLUSTER. Thanks for the suggestions.
_________________
Regards,
SMS