Re: Index usage with Wildcards - Informix vs. Oracle

spal@watson.den.csci.csc.com (CSC CIS) wrote: >When a query is done using wildcards (e.g. ...WHERE column LIKE 'HUB%'), >can the engine use an existing index on that column? Yes, it will. >To my knowledge, Informix can use the index as long as the wildcard >is not the first character in the query string, as in the above example. That is correct. If the string you are searching on is anchored to the first position (i.e. no wildcard in the beginning), then an indexed will be used. An indexed can also be invoked will substringing, as long as it is the beginning of a string. i.e. WHERE column[1,3] = "HUB" This is actually a faster method than the LIKE example. >Our Oracle people claim that Oracle cannot use an index as soon as >wildcards are involved. Is that really true? I have no idea on that one. Since I work for Informix, I will not make any snide remarks at this point. ;-) --John