Re: OnLine Optimizer with compound indices??

Todd M. Saylor (tsaylor@ecst.csuchico.edu) wrote: : I have a table with a compound index on two fields (we have many : examples such as this, often with more fields, but let's use this : example for the sake of discussion). For example, I have fields a & b. : Now, I want to do a select such as: : select a,b from my_table where a || b > "XXXX" : (The value "XXXX" spans both column a and b). : This causes the optimizer to apply a filter as it reads *ALL* index : values (at least that seems to be the case, based on the long "think" : time before the first record is returned). However, if I were to just : say '... where a > "XX"', or something similar, Informix would quickly : jump to the correct value. Since the index is on (a,b), I don't see : why my example can't work the same way. Because your where clause compares a function with a literal, and thus does not involve an indexed column. This invalidates the use of the index. Although it is obvious to us as humans that the result of this particular function has sufficiently similar properties to the column a for the index to remain useful, it is a bit much to ask of an optimizer algorithm (and I do not believe I have ever encountered one that would cope as you would wish). : If you've had any experience with similar queries (using concatenated : values), I'd appreciate any input you might have. If you modify your query to: select a,b from my_table where a || b > "XXXX" and a >= "XXX" then the index will be used (I have assumed a is >= CHAR(3)). I do not have a clear enough picture of your data or your datatypes to propose a solution that I was certain would use the second component of the index, although I think that: select a,b from my_table where (a = "XXX" and b >= "X") or a > "XXX" would work, and would perform better if there were large numbers of rows where a = "XXX" and b < "X". -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.