> If the field 'name' is an index of the table 'table', does this SQL
> statement use the index or does it scan the whole database?
> select adress from table where name like 'James*'
> I've been told Oracle database use the index and Informix don't.
> Any information ?
Informix's optimizer is purely cost based. It will probably use the
index on name for LIKE clauses as long as the match expression does not
start with a wildcard, although the optimizer is free, based on the
data distribution histograms, to decide that so many rows will match
that criteria that a table scan will actually save I/Os by not reading
the index pages. So the answer is that it depends on your data and the
key value you are searching for. If the match string begins with a
wild card character an index cannot be used. (BTW for LIKE clauses the
ONLY ANSI SQL supported wildcard characters are '%' - match any number
of characters and '_' - match any one character. The UNIX regular
expression syntax implied by the asterisk '*' in your string IS
supported by the Informix extention MATCHES clause along with character
ranges and other REGEX syntax).
FYI: This did not post to comp.databases.informix as I think you
Art S. Kagel