Informix Clustered Indexes

[Tom Lehr]

Hello All

Have been doing some reading about clustered indexing and wondering
what experiences other folks have had with them?
We currently do not use them in our high transaction OLTP world and am
a bit wary due to reading that they have to be "re-clustered after
table alters and are disturbed by ongoing updates" (from the manual,
although it does not specify much in details in regards to the ongoing
updates statement).

thanks in advance,
tom

[Jonathan Leffler]

When you alter an index to clustered, the table is rebuilt with the data in the physical order required by the index.

When the cluster operation is complete, the table goes back to normal operation.  Rows will be added and removed as seems appropriate, not necessarily preserving physical order. (Indeed, usually the physical order will not be preserved if there are delete operations; if there are only insert and update operations that do not affect the key order, you may continue to have good clustering).

Altering an index to not clustered is a trivial operation.  It has zero effect on the subsequent behaviour of inserts or deletes, though.  It is just a necessary precursor step to altering the index to clustered again.

So, at any time you want, you can alter an index to clustered, which will rebuild your table.  You may get a small performance benefit if your queries do scans over the table in the order of the clustered index (because the data pages needed will be already loaded, and read-ahead has the maximum reliability).  There is no ongoing cost to having clustered an index.  It is usually not crucial to have the index clustered.

--
Jonathan Leffler <jonathan...@gmail.com>  #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."

[Art Kagel]

Inserts and updates (and even deletes if there are subsequent INSERTs)  break the clustered-ness of a clustered index unless the clustering is on a naturally increasing key (so a serial column in the table for example.  Similarly for updates of the clustered index key values in the rows.  That means that to maintain the efficiency that a clustered index provides for sequential scanning and index range scans, when the query includes an ORDER BY clause based on the clustered index key, you have to periodically recluster the index by dropping it and recreating it (or by altering it TO NOT CLUSTER and then altering it again TO CLUSTER).

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[mpruet]

> On Tue, Jan 31, 2012 at 10:53 AM, Tom Lehr <tomc...@gmail.com> wrote:
> > Hello All
>
> > Have been doing some reading about clustered indexing and wondering
> > what experiences other folks have had with them?
> > We currently do not use them in our high transaction OLTP world and am
> > a bit wary due to reading that they have to be "re-clustered after
> > table alters and are disturbed by ongoing updates" (from the manual,
> > although it does not specify much in details in regards to the ongoing
> > updates statement).

In my previous life, I supported a reservation system for a hotel
chain. The inventory and rates information was maintained in distinct
tables per property. Rates could be added in any order at any time so
they would be inserted haphazardly.

My goal was to reduce IO to these two tables because the vast majority
of lookups would be going against those two tables. Also, the
majority of the rate/inventory was against data in the next two weeks,
even though we had two years of future inventory and rates.

So I would periodically re-cluster those tables by date. That way I
could minimize the number of pages that had to be loaded into the
buffer for rates and inventory. And was able to have a high write as
well as read cache hit rate.

>
> > thanks in advance,
> > tom
> > _______________________________________________
> > Informix-list mailing list

> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list