Re: attached index
- From: "Art S. Kagel" <kagel@xxxxxxxxxxxxx>
- Date: Mon, 21 Aug 2006 10:57:21 -0400
Floyd Wellershaus wrote:
We have a query that runs kind of slow, which is exacerbated by the fact that it has to run millions of times.
It's searching on partial firstname and lastname.
It is using a composite index but I notice that this index as created with a storage parameter of "in table".
Wouldn't I expect to get better performance by detaching that index to another dbspace ?
In-table indexes perform better for index scans, ie identifying a starting key in a partial key or >= search in the index and scanning the nodes sequentially until a non-qualifying key is found. This works IFF the data pages are closely colocated with the index pages that reference them. When a table grows with few deletes and the key is the natural key of the table, or when a table is clustered and the index being scanned is the cluster key index, (ex: a datetime, an invoice number, or a serial column) then this is the case and the likely reason that Online and early IDS indexes defaulted to this modality. This was a common way online was used for 3rd party applications originally.
However, if these conditions do not hold, if the data pages and index pages are not colocated - as happens when there are extensive deletion or if the index being scanned is not a natural key - then detached indexes will perform better. The changes in the way IDS is used over the years, the popularity of larger more complex schemas and queries, is likely the reason that the default was changed to detached or semi-attached.
Art S. Kagel
.
- Prev by Date: Re: Informix in the press...
- Next by Date: Re: attached index
- Previous by thread: Re: attached index
- Next by thread: Re: attached index
- Index(es):
Relevant Pages
|
