Re: "Clustered" Indexes

Amy Barron <amyb_at_hattonblue.com> wrote:
>
> For Oracle 7.3 or 8.0, can anyone suggest a way to create a
> "clustered" index on a database table, similar to the clustered
> indexes in SQL Server?
> By this I mean that the data in the table is physically sorted by
> the clustered column. This allows for faster inserts when multiple
> users are updating the table. In SQL Server, only one clustered
> index is allowed to exist on a table, and the index itself needs
> to be specified non-unique.

I don't think that you want to.

First, to clear up some points about Sybase clustered indexes: rows are stored in the data pages in sorted order, thus the data pages form the leaf nodes in the index. It results in slightly faster retrieval, as the DBMS doesn't need to read the leaf nodes of an index to find the data rows.

It doesn't necessarily make for faster inserts, and in my opinion slows down most inserts and causes contention. If your cluster values are monotonically increasing, then all inserts will be to the last page of the table, and users will queue up behind one another (this can lead to deadlock aborts in addition to simple waits). If the cluster values are "random", then each user will typically update a separate page -- however, if they insert into the middle of the page, all data on the page must be moved ... perhaps to a different page!

But what has this to do with Oracle?

This second use, spreading inserts around the data pages, isn't necessary in Oracle, which provides row-level locking (Sybase is page-level). Multiple users can write to the same in-memory block of table data (provided they write to different rows), and in many cases these writes will not interfere with each other.

If you really want to spread inserts around, you can increase the FREELISTS parameter on the table and its indexes. This means that the table will contain multiple free-block lists for inserts. However, I've found that this can actually slow down the system, as you've just caused twice (or more) the number of dirty blocks.

In short, unless you are sure that Oracle is having contention for specific blocks, there's really no reason to try to force it to spread its writes.

-kdg Received on Tue Dec 15 1998 - 00:00:00 CST