Re: Table clustering idea

On Jun 27, 2006, at 9:39 AM, Jim C. Nasby wrote:
> I think one of the issues might have been: how will you handle other
> indexes on the table when you can no longer point them at an item
> (since
> items will need to move to maintain an IOT).

There are clean ways to handle this. The table is organized on the
primary key, a typical requirement for IOTs. Any indexes you add to
IOT reference the primary key of the heap tuple. Since the heap and
PK index are the same thing, external indexes use the PK as the tuple
identifier.

The only caveat is that this creates performance asymmetries. IOTs
have significantly faster access through their primary keys but
slower external index access since two B-Trees have to be traversed.
An IOT is typically only used for tables that are only accessed
through their primary key. Not supporting external indexes on IOTs
is a functional implementation (and probably recommended in
practice), though most real implementations allow external indexes if
not always in their first version.

J. Andrew Rogers
jrogers(at)neopolitan(dot)com