Reorganize composite indexes

[huber1]

I'd like to reindex (reorganize) the indexes of the database primary and foreign key indexes. Some of them are composite indexes (both primary and foreign key). There is mentioning in the Sybase DOCX of reorganizing composite indexes here:

http://dcx.sybase.com/index.html#1201/en/dbusage/improving-using-perform.html

But if I enter these terms in Search there I get no results. I am looking for the commands that do the reorganizing of indexes so I can use them in a script? I know I can rebuild indexes "manually" in Sybase Central. Regards, Robert

[VolkerBarth]

If you are about to reorganize indexes of several tables, you might want to check whether some of them use the same underlying physical index - say, a PK index on a parent table and the according FK index on a child table. If such cases exist, I would assume that it is enough to reorganize one of those... (I can't tell how expensive a REORGANIZE TABLE on an already well-organized table/index is but due to the necessary locking/exclusive access I would prefer not to reorganize unneccessarily:))

[Breck_Carter]

> some of them use the same underlying physical index - say, a PK index on a parent table and the according FK index on a child table.

Those are different physical indexes because the data comes from different tables.

I know, it's Monday 🙂

[VolkerBarth]

Of course you are right - what I really meant was a child table with a 1:1 relationship to a parent table, when the child's PK is also a FK to the parent - then both automatically created logical indexes on the child table (PK, FK) will share the same physical index.

[Breck_Carter]

See REORGANIZE TABLE statement... in spite of the name, it can be used to reorganize indexes.

[huber1]

I overlooked that, thank you for pointing it out.

[VolkerBarth]

Another means would be the ALTER INDEX statement with the REBUILD clause - I don't know whether this behaves differently from a REORGANIZE TABLE with the corresponding indexes...

[huber1]

Thanks Volker for this alternative. Both statements do work. I also wonder what might be the difference, if there is one?

[VolkerBarth]

A good question on its own - I simply don't know. Feel free to ask that as a separate question:)