Indexing issues, help
Bounce. > > Hi, > Can anyone clear me up about index, clustered index. > > 1. What is the best timing to use clustered index? > 2. index A and index B has the same indexing columns but different order, > such as (a, b, c) and (c, b, a), where a, b, c are columns. > Will ithe proformance make difference that the table has these two > index toghther? > 3. What is the best indexing stradegy? > > Jack > I might suggest analyse_idx a utility you can pick up from the archives (ftp.mathcs.emory.edu/pub/informix). It analyses indices looking for efficiency, compares them against your usage of them in the source code (if source code exists and you can point it out) and then gives you some useful information on the columns you've chosen or others you might consider choosing. It requires 4gl to compile and run. 1) Clustering is a one time shot. Altering an index to cluster causes the table to be re-written in order of the clustered index. That ordering is not maintained through subsequent additions/updates/deletions. Hence it is quite useful on large static tables, but less so on more dynamic tables. 2) Yes and no. Each additional index is overhead during a write to the table. A composite index on a, b, and c will be used whenever there is a known value for a, or when there is a known value for a, b and c. It will not be used when you query using just c. The best way to plan your indices is to look at how they are used by the users and index accordingly. Hence why I wrote analyse_idx. 3) I think I just said that. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com Currently on loan to PLD/PE _____________________________________________________________________________ If anything can go wrong, fix it. To hell with Murphy. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________