Free space...Clustered Data

Do many companies use clustering indexes...via table data ordered by a key (clustered)? Is there a performance boost in Informix when the data is clustered -- and then retrieved via cluster sequence? If clustering helps, will Informix allow us to specify the % of freespace on a page, to allow contiguous cluster sequence via inserts and updates? And thus, use sequential prefetch more efficiently because the data is "physically" stored in true cluster sequence. If there is efficient amount of freespace on a page, you gain: Better clustering of rows (giving faster access) Fewer overflows Less frequent reorganizations needed Less information locked by a page lock Fewer index page splits But, The disadvantages are: More disk space occupied Less information transferred per I/O More pages to scan Possibly more index levels Less efficient use of buffer pools and storage controller cache sending to informix-list

Urich Ann wrote: > Do many companies use clustering indexes...via table data ordered by a > key (clustered)? > > Is there a performance boost in Informix when the data is clustered -- > and then retrieved via cluster sequence? The performance benefit is marginal - and debatable. It is certainly far from critical that tables are kept in clustered order. Some other DBMS do seem to depend on clustering for optimal performance - IDS does not. > If clustering helps, will Informix allow us to specify the % of > freespace on a page, to allow contiguous cluster sequence via inserts > and updates? And thus, use sequential prefetch more efficiently > because the data is "physically" stored in true cluster sequence. Given the falsity of 'if clustering helps', maybe I should leave well alone. However, I'll jump in feet first... No, IDS does not allow you to specify a percentage of free space to leave on a page. There is the index fill threshold (FILL_FACTOR, give or take an underscore) that might give some value in indexes. > If there is efficient amount of freespace on a page, you gain: > Better clustering of rows (giving faster access) > Fewer overflows > Less frequent reorganizations needed > Less information locked by a page lock > Fewer index page splits > But, > The disadvantages are: > More disk space occupied > Less information transferred per I/O > More pages to scan > Possibly more index levels > Less efficient use of buffer pools and storage controller cache Let's suppose that this was implemented - that's a highly hypothetical supposition. When would it be OK to go over the threshold? Never? Sometimes? When? How would you express it? Per fragment or partition, per table, per dbspace, per database, per instance? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/