Queries on a person table often filter on last_name and first_name together, and many others filter on last_name alone; none filter on first_name alone. Names are not unique. The DBA wants a single index that supports both query patterns. Which statement should the DBA use?
Select an answer to reveal the explanation.
Short Explanation
A composite index works like a phone book sorted by last name, then first name. You can look up 'Smith' alone or 'Smith, Anna' together, but you can't jump to every 'Anna' without reading the whole book. So you lead with the column that every query uses, and you don't make it unique when people share names.
Full Explanation
A composite index sorts entries by its first column, then by the second within each value of the first. Queries can seek efficiently on the leading column alone or on the leading column combined with the next, but not on the second column by itself. Because every query in this workload filters on last_name, either alone or with first_name, an index on last_name followed by first_name supports both patterns with seeks. Placing first_name first reverses the order; queries filtering on both columns could still seek, but queries filtering only on last_name could not use the leading column and would scan the index or table, failing the requirement to support both patterns. Declaring the index UNIQUE would enforce that no two people share the same last and first name combination; because names are not unique, creating the index would fail if duplicates exist, and later inserts of legitimate duplicate names would be rejected. An index on first_name alone supports neither pattern well: no query filters on first_name alone, and it cannot help locate rows by last_name. Exam caveat: column order in a composite index should follow how queries filter, with the columns used in equality predicates by the most queries first; selectivity is a secondary consideration. Operational check: create the index in non-production, confirm execution plans show index seeks for both query patterns, and monitor insert overhead on the table after deployment.