RE: Attached vs. Detached index

Probably the easiest is dbschema -d database -t table -ss - which will show you the strategy for your index - if it has one then it's detached. According to my info, 'detached' really means that you have specified storage for the index - which may differ (or not) from the storage you have for the table. Whether it's better really depends on your situation. There are a lot of options: (From the masters series doc) Attached index on a non-fragmented table Good for small tables or OLTP attached index on a fragmented table Large DSS or OLTP detached " " non-fragmented " Small DSS/OLTP with high hit rates on idx and data pages detached " " fragmented " DSS with some selective queries detached fragmented " " fragmented " Mixed OLTP/DSS with data fragmented for DSS and index fragmented for OLTP or selective queries. det frag non-frag OLTP with mainly indexed reads (end of plageurism) And of course the item skipped in the above doc is NO indices on a fragmented table - but why then mention it? A detached index requires an extra 4 bytes in each index to point to the fragment where the data resides. Undex XPS all indices are detached. With an attached index you are limited to the fragmentation strategy of the table. If there is none, then you get none. However you DO get a slightly smaller index size. Detaching the index (as hinted in #5 above) can give you some nice options in terms of fragmenting the index one way for indexed reads and your data a different way for sequential scans. cheers j. > -----Original Message----- > From: Bogdan Neagu [mailto:bogdan.neagu@alcatel.com] > Sent: Wednesday, January 10, 2001 11:01 AM > To: informix-list@iiug.org > Subject: Attached vs. Detached index > > > Hi all. > > In IDS 9.21 (FC2), how can I determine if an index was > created attached > or detached ? > > What are the pros and cons for attached vs. detached ? > > Thanks in advance, > > Bogdan Neagu > > >