Re: Optimizer behavior - DOH!

Remember, Informix indexes are value-ordered B-trees. Also, the optimizer may not use an index if it can get good enough fragment elimination or the index is weakly selective. Proper statistics collection will potentially make a huge difference. Having said that, yes, Informix will use partial indexes as long as you specify the first column(s) of the index. Given your example, assume we have an index on SKU, Whse#,Date in that order. If I specify just a date, or date and whse#, it will not use the index. If I specify SKU and any of the others it *might* use the index if it's selective enough. Best case is of course specifying all three columns. Order in your where clause should not matter, order of the columns in the index does. Bottom line, you must pay careful attention to your data's cardinality and the most common access patterns of your users. >==========Bob Payne, 8/8/96========== > > >Does the optimizer use partial indexes? In other words, if I have an >index on the sku column, warehouse column and the date column, >do I have to >include all three of those in my where-clause? > >In former times I've selected using only one of the three and >the Optimizer >will use the index. Now, sometimes it does and sometimes I have >to include >all three in order to use the index. Sometimes the order >matters and other >times order of the columns makes no difference at all! Dopey! > >Does anybody have a clue as to how the optimizer makes its choices? > >-- >Bob Payne Software Development Manager >PC Service Source email: bpayne@netcom.com >2350 Valley View >Dallas, TX 75234 >(214) 481-4707 WWW: http://www.pcss.net/~bpayne John Susag - john.susag@minneapolismn.ncr.com NCR Enterprise Consulting "We are the music makers and we are the makers of dreams" Willy Wonka