Examples Using Multi-column Indexes

Go Up to Creating Indexes

The first example creates a multi-column index, NAMEX, on the EMPLOYEE table:

CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);

The following query will be optimized against the index because the ORDER BY clause references all of the indexed columns in the correct order:

SELECT LAST_NAME, SALARY FROM EMPLOYEE
WHERE SALARY > 40000
ORDER BY LAST_NAME, FIRST_NAME;

The next query will also process the following query with an index search (using
LAST_NAME from NAMEX) because although the ORDER BY clause only references one of the indexed columns (LAST_NAME), it does so in the correct order.

SELECT LAST_NAME, SALARY FROM EMPLOYEE
WHERE SALARY > 40000
ORDER BY LAST_NAME;

Conversely, the following query will not be optimized against the index because the ORDER BY clause uses FIRST_NAME, which is not the first indexed column in the NAMEX column list.

SELECT LASTNAME, SALARY FROM EMP
WHERE SALARY > 40000
ORDER BY FIRST_NAME;

The same rules that apply to the ORDER BY clause also apply to queries containing a WHERE clause. The next example creates a multi-column index for the PROJECT table:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

The following query will be optimized against the PRODTYPEX index because the WHERE clause references the first indexed column (PRODUCT) of the index:

SELECT * FROM PROJECT
WHERE PRODUCT ='software';

Conversely, the next query will not be optimized against the index because ­PROJ_NAME is not the first indexed column in the column list of the ­PRODTYPEX index:

SELECT * FROM PROJECT
WHERE PROJ_NAME ='InterBase 4.0';