Hello,

I have a question about "clustered indexes.

After I looked into the documentation, I understand it this way:
when using "clustered indexes" Sybase tries to store the records in the
order according to the index.

Does this mean then that Sybase moves other records when you insert a new
record so that the new one gets the right place inside the database.
Thus, if the table continuously reorganized (under the index) and all
records of a table are no gaps in a row?

Or what exactly does it mean when an index is geclustered?

Regards

I don't know if this is still an open question or if it has been
answered on another thread ... but ... let me give this a shot.

At one level, there is no guarantee that the rows in the table
pages are ordered even if you have created a clustered index.
You can see that from the fact that simply altering an index
to be a clustered index, does not (alone) reorder the rows
in the table pages. It does tell the optimizer to treat the index
as if that was the case, and that may cause the index to be
treated more preferentially with some queries.

It is true that inserts into a table that has a clustered index
will tend to be located on the same pages as related keys,
but there may be a lot of reasons why that may not be able
to be achieved. Inserts should be close to the ideal pages,
but if pages are full then rows may be inserted on a different
page near by.

Other factors that can affect order include

- degree of which the pre-existing rows are actually ordered
already
- the frequency with which row deletes happen [that can free
up space on table pages for those indexes]
- the 'spread' of those deletes [the number pages affected and
the distribution of the holes created]
- if there are updates to values that are in clustered index columns
- updates of variable length columns that cause the row size to
change
- the amount of space held in reserve (by PCTFREE)
- the way keys are assigned [ordered or usage based
randomized]

Historically rows do not move once inserted unless a reorganize
table does so. One way to effect a 'move' for an update operation
that changes clustered key values is to always delete those rows and
reinsert them with the new key values.

Yes a frequently reorganized clustered index will
reorder the rows in the table pages. But you probably
should not reorganize too often. Most times adding
an order by to the queries will still be sufficiently fast
to cover the case of a few wayward rows and stragglers
until this situation gets bad enough that you would benefit
from a reorganize table execution.


HTH


"Ingmar" <ingmar.beck (AT) spam (DOT) de> wrote