Indexes On Expressions

Indexes On Expressions

Normally, an SQL index references columns of a table. But an index can also be formed on expressions involving table columns.

As an example, consider the following table that tracks dollar-amount changes on various "accounts":

CREATE TABLE account_change(
  chng_id INTEGER PRIMARY KEY,
  acct_no INTEGER REFERENCES account,
  location INTEGER REFERENCES locations,
  amt INTEGER,  -- in cents
  authority TEXT,
  comment TEXT
);
CREATE INDEX acctchng_magnitude ON account_change(acct_no, abs(amt));

Each entry in the account_change table records a deposit or a withdrawal into an account. Deposits have a positive "amt" and withdrawals have a negative "amt".

The acctchng_magnitude index is over the account number ("acct_no") and on the absolute value of the amount. This index allows one to do efficient queries over the magnitude of a change to the account. For example, to list