E
Eric Andruscavage
Guest
I am always curious when I see a unique or nearly unique field at the end of an index. It can cause the index to be multiple times the size of an index that doesn't contain the field and adds almost no performance to the index. A lot depends on the data, the order the data was added/deleted/updated, and the distribution of data in the index. You don't say what 'a terribly long time' is, but if you mean more than one second then you have a hardware fault or not enough ram or some other problem. If it's been awhile since the last index rebuild the index tree will be unbalanced and the query may need to traverse many more levels to get to the index block that contains the values you are looking for. The index block space utilization can be low if there are updates to any of the 3 fields. Run the IDXCOMPACT option of proutil. I'm not the expert here, but I believe that even after an index rebuild the index to your table would contain many more levels than an index without field3. I would suggest adding an index that only contained field1 and field2.
Continue reading...
Continue reading...