Re: Indexing strategy

Posted in 1993

Alan Popiel — Wed, 28 Apr 93 09:47:04 MDT — source: Informix-list mailing list archive (1991-1998)

->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. As the writer of the above claim, let me try to qualify and clarify it. 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. Optimizers tend to be heuristic and rule based, and thus very flexible and subject to change. I could certainly understand if Informix declines to state exactly how their optimizer works. They may even consider the details to be trade secrets, and thus only provide general quidelines to customers. In the spirit of free exchange of *non-proprietary* information, I submitted the info I had as a guide to others, while not claiming to have the final word on the subject. Regards, Alan +------------------------------+---------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, LSC | ( Please note: My opinions do not ) | | P.O. Box 179, M/S 5422 | ( represent official Martin policy. ) | | Denver, Colorado 80201-0179 | Voice: 303-977-9998 | +------------------------------+---------------------------------------+