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

On Tue, Jan 31, 2012 at 07:53, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote:


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.leffler (AT) gmail (DOT) 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."

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.



On Tue, Jan 31, 2012 at 10:53 AM, Tom Lehr <tomcaml (AT) gmail (DOT) com> wrote: