D
dbeavon
Guest
>> Have you tried the query with a single three-field index? Yes, that will work fine too (probably faster, as you say). There is already an index with these three fields at the top, and would satisfy equality conditions. I'm worried that the single-field indexes were selected by the compiler in PREFERENCE to the existing one with the three fields at the top. There are a few dozen programs where these single-field indexes are showing up in the xref's. And removing the indexes is a nail-biter because it will force the compiler to do something different in all those cases (probably each would need to be reviewed and tested separately). I was hoping the decision would be made easier, based on the fact that these programs would be extremely slow when joining three indexes together. But they are not and I'm wondering why. I think there is some subtle magic going on related to the the JOIN operation on the ROWID's. I would like to understand the magic and *quantify* it if possible, so I know how to compare performance against the case where I'm using a conventional index. The problem with quantifying the magic is that the index reads seem to be under-stated for these types of queries. (I think there is some short-circuiting going on where a read isn't counted at all - unless the index is traversed all the way down to the ROWID. I don't think it counts the cases where the JOIN operation is evaluated using data that is above the level of the ROWID.).
Continue reading...
Continue reading...