Many queries filter on more than one column: tickets of a flight and seat, bookings of a customer by date. A composite index covers several columns in one B-tree, and a unique composite index also guarantees that no two rows share the combination, such as two passengers in the same seat of one flight.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create [unique] index [if not exists] index on table (column1 [, column2 ...])
The entries are sorted by the first column, then the second, and so on. Primary key and unique constraints create unique indexes automatically.
A Unique Index on Flight and Seat
Example:
create unique index tickets_flight_seat_ux on tickets (flight_id, seat_no); select index_name, uniqueness, column_name, column_position from user_indexes join user_ind_columns using (index_name, table_name) where table_name = 'TICKETS' order by index_name, column_position;
Output:
Index TICKETS_FLIGHT_SEAT_UX created. INDEX_NAME UNIQUENESS COLUMN_NAME COLUMN_POSITION _________________________ _____________ ______________ __________________ TICKETS_BOOKING_IX NONUNIQUE BOOKING_ID 1 TICKETS_FLIGHT_IX NONUNIQUE FLIGHT_ID 1 TICKETS_FLIGHT_SEAT_UX UNIQUE FLIGHT_ID 1 TICKETS_FLIGHT_SEAT_UX UNIQUE SEAT_NO 2 TICKETS_PK UNIQUE TICKET_ID 1
USER_INDEXES and USER_IND_COLUMNS show every index of TICKETS with its columns in order. The new index has FLIGHT_ID in position 1 and SEAT_NO in position 2, and rejects any second ticket for the same seat on the same flight.
Column Order Matters
Because entries are sorted by the leading column first, a composite index serves conditions on:
- All of its columns: flight_id = :f and seat_no = :s.
- Its leading columns: flight_id = :f, which reads a range of the index.
A condition on seat_no alone cannot use the sort order; at best the optimizer skips through the index for each flight (an index skip scan) or reads it fully. Put the column that is filtered most often, alone or with others, first.
Things to Know
- A composite index on (a, b) makes a separate index on (a) redundant.
- If a query needs only the indexed columns, Oracle can answer it from the index without reading the table.
- Drop the example index with drop index tickets_flight_seat_ux.
Related Guides
Conclusion
A composite index covers several columns, sorted by the first, and a unique one enforces uniqueness of the combination. Order the columns by how queries filter, leading with the one used most, and check them in USER_IND_COLUMNS.
