Problem solve Get help with specific problems with your technologies, process and projects.

Composite primary keys and indexes

Does one element of a composite primary key still act as an index? I'm just doing a database tuning exercise. My hunch is the answer is no, on the grounds that one element of a CPK no longer equals a PK, so why should it continue to work as an index?

A primary key is not an index, though all database management systems of which I know implement unique indexes to enforce the primary key constraint. A composite primary key would therefore result in an index of multiple columns. As with all B-Tree style indexes, the columns of a composite index are ordered.

If you had an index of LAST_NAME, FIRST_NAME (primary key or not) in that order, and your query includes a where clause that states LAST_NAME = 'SMITH' and FIRST_NAME = 'JOHN', then the index would probably (almost definitely) be used. In the case that you only stated LAST_NAME = 'SMITH', the index would probably be used as well, as LAST_NAME is the first element in the composite index. If in the case you only used FIRST_NAME = 'JOHN' in the where clause, the index would most likely not be used. To answer your question, when using the first n elements of an index in a where clause, the DBMS will most likely use the index to retrieve the result set, even if not all the columns of the index are specified.

For More Information

Dig Deeper on Oracle and SQL