SQL Pocket Guide, 2nd Edition

Indexes, Creating

The basic CREATE INDEX statement syntax is:

CREATE INDEX falls_name ON upfall
   (name, open_to_public);

In this syntax, falls_name is the name of the index. The table to be indexed is upfall. The index is on the combined values of name and open_to_public.

Oracle and PostgreSQL allow you to assign an index to a tablespace:

CREATE INDEX falls_name ON upfall
   (name, open_to_public)
   TABLESPACE users;

Oracle and PostgreSQL also allow you to index column expressions:

CREATE INDEX falls_name ON upfall
   (UPPER(name), open_to_public);

This particular index is useful for resolving queries in which the WHERE clause predicates involve the expression UPPER(name).