IDS Forum

Re: attached indexes versus detached indexes

Posted By: Art Kagel
Date: Tuesday, 20 April 2010, at 10:30 p.m.

In Response To: attached indexes versus detached indexes (ROGER VILCA)

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

Messages In This Thread

• attached indexes versus detached indexes
  ROGER VILCA -- Tuesday, 20 April 2010, at 6:07 p.m.
  • Re: attached indexes versus detached indexes
    John Miller iii -- Tuesday, 20 April 2010, at 7:34 p.m.
  • Re: attached indexes versus detached indexes
    Art Kagel -- Tuesday, 20 April 2010, at 10:30 p.m.
  • Re: attached indexes versus detached indexes
    ROGER VILCA -- Wednesday, 21 April 2010, at 11:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.