attached indexes versus detached indexes

Hi, please provide information according your experience In general, for a non fragmented table, do you recommend an attached index or an detached index ? Which are the advantages or disadvantages in disk space, speed of reading, speed of writing, etc. For example: create table table1 ( a integer, b integer, c integer, d integer) ) create index index1 on table table1(a) or create index index1 on table table1(a) in dbspace1? Thanks in advance

You really did not say which version you are on. In the older versions attached actually mean interleaving data and index pages. In the current versions, it really means moving the index to a different dbspaces. The index an data are by default stored in different partitions (having their own extents). If you do move the index into a different dbspace then the index key grows by four bytes. The root and leaf pages do not change in size. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/20/2010 03:07:16 PM: > [image removed] > > attached indexes versus detached indexes [19761] > > ROGER VILCA > > to: > > ids > > 04/20/2010 03:08 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi, please provide information according your experience > In general, for a non fragmented table, do you recommend an attachedindex or > an detached index ? > > Which are the advantages or disadvantages in disk space, speed of reading, > speed of writing, etc. > > For example: > > create table table1 ( > a integer, b integer, c integer, d integer) > )> > create index index1 on table table1(a)> > or > > create index index1 on table table1(a) in dbspace1> ? > > Thanks in advance > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Just to expand on what John said: In the original Informix 4, 5, 6, and early 7.xx releases index pages were interleaved with data pages under the assumption that locating the index pages near the data pages they referenced would allow the engine to take advantage of read-ahead to have the data pages prefetched into cache when one referenced them from the index pages. As databases grew, however, it became apparent that index pages were referencing data pages that were physically very far away, so the opposite was happening. When one was navigating the index tree read-ahead was not only not pre-fetching the associated data pages, but was not pre-fetching the next index pages into the cache eithers. Performance was suffering. Therefore, in the 9.xx and 7.30 releases of IDS attached indexes were changed so that index pages were assigned their own complete extents within the table's partition(s) so that index pages were at least contiguous within the table to take advantage of read-ahead prefetch. These were known as semi-attached indexes. In 7.31 and 9.30 and all later releases, however, even "attached" indexes were actually created as detached indexes with their own separate partitions, just created in the same dbspace(s) as the table itself. So, there really is no longer any physical difference between an attached and a detached index and performance is substantially the same with one proviso. That is that if you can detach the index to physically separate dbspaces positioned on independent spindles on independent channels from the data dbspaces then you may be able to increase peak throughput performance and increase parallelism within the engine versus having the data and index partitions on the same physical disk structures. The biggest performance gain from such physical separation will typically be felt during peak loads, however, not during normal operations. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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, Apr 20, 2010 at 6:07 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote: > Hi, please provide information according your experience > In general, for a non fragmented table, do you recommend an attached index > or > an detached index ? > > Which are the advantages or disadvantages in disk space, speed of reading, > speed of writing, etc. > > For example: > > create table table1 ( > a integer, b integer, c integer, d integer) > )> > create index index1 on table table1(a)> > or > > create index index1 on table table1(a) in dbspace1> ? > > Thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd17c2e0c0dd10484b5f903

Art, John Thank your for your excellent explanation