Re: Indexes: Attached or Detached?

["Shulzhenko Vasyl" <vasilis@softline.kiev.ua>]

Thomas

I prefer detached indexes (preferably on dbspaces on different disks)
because of the following 2 reasons:

1) Some degree of distribution of disk writes is achieved, because when you
insert it can insert data and update the index in parallel. Also assuming a
fairly even distribution of sequential scans, index reads and index-only
reads, it will provide parallelism.

2) If you are using detached indexes and name them properly (ie different
from the table), you can see how much each index was hit by querying the
sysptprof table.

I have done this for one customer who was having performance problems, and
without knowing much about the application, I determined the tables which
were heavily hit (from the sysptprof table again) and spread them out
across dbspaces across different disks, I then took the indexes from tables
in dbspace1 and placed them in dbspace2, and so on till indexes from
dbspace n into dbspace1. We did notice a significant performance rise,
although I am not sure if it was due to reallocating the tables across
different dbspaces or due to detaching the indexes.

HTH

Sujit





"Thomas J. Girsch" <tgirsch@iname.com> on 04/20/99 11:11:08 AM

Please respond to "Thomas J. Girsch" <tgirsch@iname.com>

To:   informix-list@iiug.org
cc:    (bcc: Sujit Pal)
Subject:  Indexes:  Attached or Detached?




I wanted to pose a question to the group:  Do you prefer to use attached
indexes or detached indexes?  If you have a preference, why do you have
that
preference.  Have you noticed any key differences/benefits in one vs. the
other?

Thanks in advance,

- Tom Girsch
  tgirsch@iname.com