Composite Index

What is a composite index?

• A composite index is an index built on two or more columns of a table instead of just one.
• The key of the index is the combination of the column values, in the order you list them.
• Composite indexes are useful when queries frequently filter or sort on several columns together.
• A primary key made of multiple columns is also called a composite key - for example PRIMARY KEY (CUSTOMER_NO, BALANCE_PERIOD).

Column order matters

• DB2 can use a composite index efficiently only from the leftmost column onwards.
• An index on (DEPTNO, JOB) helps queries filtering on DEPTNO, or on DEPTNO and JOB together - but not queries filtering on JOB alone.
• Put the most selective and most frequently filtered column first in the key list.
• Keep the number of columns small. Very wide composite keys make the index large and slower to maintain.

Example

• Composite index on two columns:-
  
  CREATE INDEX IX_EMP_DEPT_JOB ON EMP (DEPTNO, JOB);
• This index speeds up queries like:
• SELECT EMPNO, ENAME FROM EMP WHERE DEPTNO = 10 AND JOB = 'CLERK';