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

asked 16 Jun '14, 10:31

robert's gravatar image

robert
578293748
accept rate: 0%

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

(16 Jun '14, 11:07) Volker Barth
Replies hidden

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

(16 Jun '14, 14:13) Breck Carter

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.

(16 Jun '14, 16:07) Volker Barth

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

permanent link

answered 16 Jun '14, 10:58

Breck%20Carter's gravatar image

Breck Carter
26.2k430600863
accept rate: 20%

I overlooked that, thank you for pointing it out.

(18 Jun '14, 04:57) robert

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

permanent link

answered 17 Jun '14, 04:00

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 17 Jun '14, 04:01

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

(18 Jun '14, 05:17) robert
Replies hidden

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

(18 Jun '14, 06:59) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26

question asked: 16 Jun '14, 10:31

question was seen: 596 times

last updated: 18 Jun '14, 06:59