Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Composite Indexes

You can create an index on multiple columns in a table. If you want to create an index on the EMPLOYEE_ID and DEPARTMENT_ID columns in the employees table, for example, you can do so, and the result is called a composite or concatenated index. Here's an example:

SQL> create index test_idx1 on employees(employee_id,department_id); Index created. SQL>

You can create composite B-tree indexes as well bitmap indexes. The optimizer will take into account a composite index when the WHERE clause in a query refers to all the columns in the index or even the leading column. The previous example showed a composite index with just two columns, but you can have even more columns if you wish. The following example shows a composite index ...