Re: Indexing strategy

Alan Popiel writes: |> ->From: kpf@oasis.icl.co.uk (Karl Funk) |> ->Subject: Re: Indexing strategy |> ->Date: 28 Apr 93 09:46:31 GMT |> -> |> ->1rk2ciINNrhr@emory.mathcs.emory.edu writes: |> ->>: |> -> Stuff deleted.. |> -> |> ->>: If you define the unique 3-part index before the non-unique |> ->>: 2-part index, the query optimizer may already be using the 3-part |> ->>: index (so I have heard), since the optimizer finds the 3-part index first, |> ->>: and it will do the job. |> ->>: |> -> |> -> Is this true? Can some one with the source confirm this? |> -> Exactly how does it decide which index to use? |> -> |> ->Regards, Karl Funk. |> |> Unfortunately, I can not at the moment find any official Informix doc that |> says just what I have said. HOWEVER, in at least two classes dealing with |> optimization and related subjects, I have been told that the database engine |> can use the leading fields of a composite index just as though they were a |> separate index. |> |> I think that one (non-Informix) instructor said that he believed the query |> optimizer would stop after finding the first index that would "do the job", |> and not find a later index that was specially crafted for the purpose. Informix will maintain the number of unique values for the first column in any index. This is used to determine an approximate selectivity for that index. We also store second min and second max value for the entire key of the index. This gives us an approximation of the range of values in the index. If you selected from the table with colA = "value", and colA is the first part of a composite index AND an index of itself, then computing the selectivity of these indexes (# unique values / total # rows) will produce the same selectivity, and from that perspective the two indexes would be equal in use. Of course, other criteria would be considered, such as the number of levels in the btree. I find it unlikely that the index would choose a blantently poorer index unless it was dealing with very out-of-date statistics. The description you cite from an instructor sounds like a good description of the "old" (pre-4.0) optimizer. I will venture to say that it does not apply to the current (cost-based) optimizer. Dave Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney