Used Indexes

If possible, write SQL statements in a way that indexes can be used.

Background

When an index has been defined on one or more columns, there are a couple of situations where the index will not be used. For example, an index will not be used when:

•A column value is contained in a function call.

•A column value is contained in a calculation.

•Column values are concatenated.

•Column values are excluded (NOT, <> , != ).

•Column values are not the first within an index.

If an index has been defined on more than one column, values are indexed as concatenations of the values in the individual columns. Concatenation is in the order in which the columns are mentioned in the CREATE INDEX command.

This means that any WHERE clause that implies a search for the entire indexed string, or for the first column(s) of the index ("front-end searches"), will make the index available.

Any WHERE clause that implies a search for the last part of the indexed string but not the front part, will NOT make the index available.

Incidentally, this is also why LIKE 'A%' constructs make an index available whereas LIKE '%A' constructs ("back-end search") do not.

Examples

Rewrite a WHERE clause like:

WHERE SUBSTR(name,1,3) = 'AUS'

to:

WHERE name LIKE 'AUS%'

Rewrite a WHERE clause like:

WHERE price * 2.21026 < 1000

to:

WHERE price < 1000/2.21026

Rewrite a WHERE clause like:

WHERE ColA || ColB = 'ABC'

to:

WHERE ColA='A' AND ColB='BC'

Rewrite a WHERE clause like:

WHERE amount != 0

to:

WHERE amount > 0