Very Computer

by **DECRAENE Bru** » Tue, 07 Apr 1998 04:00:00

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 ?

by **Art S. Kage** » Tue, 07 Apr 1998 04:00:00

> 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
intended.

Art S. Kagel

by **Jerry Gitome** » Tue, 07 Apr 1998 04:00:00

Since release 7.0 of Oracle (back in 1992) Oracle has had a cost based
optimizer which has improved with every release. If using the index
will result in no more than 10% of the rows being retrieved the index
will be used. If more than 10% of the rows will be retrieved the index
probably will not be used. This is because if more than 10% of the rows
will be retrieved it is faster for Oracle to do a full table scan.

Incidentally, your SQL statement has an error in it. You should use the
percent sign for the wild card instead of the asterisk.

Regards

Jerry

> 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 ?

--
Jerry Gitomer Since I know how to spell DBA I became one.

Very Computer

comp.databases.informix comp.databases.oracle

comp.databases.informix comp.databases.oracle

comp.databases.informix comp.databases.oracle

comp.databases.informix comp.databases.oracle