Re: table indexes

On 10-Jul-2014 13:16 -0500, Dave wrote:

I have a table with an index already on Columns 1, 2, 3 and 4.

Now I've been told we need another one on Columns 1, 2 and 3.

Is that necessary? I don't see why.

Likely the second is redundant, but as the party being "told", the /need/ is indeterminate without further discussion; what you do or "don't see" with regard to "why" may not be relevant, yet there may be value in consulting with whomever originated the request, about their reasoning for an apparent redundant definition.

There are a variety of nuanced reasons for which some seemingly redundant INDEX [or simply Access Path] definitions are desirable separately; possibly even for more reasons, may be desirable as separate database *FILE objects. If the second merely effects a /shared/ access path of the first, then there is [effectively no additional performance impacts [i.e. only the original AccPth is /maintained/ for insert and update] and there is [effectively] no additional storage requirements [effectively just the additional storage for the *FILE and the *MEM objects], so its addition is unexceptional beyond a separate entity to track.