Re: Indexes: Attached or Detached?

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