What is composite index?
- A composite index is comprised of two or more columns.
- Composite indexes should be avoided as they are large in size and can be have a performance overhead.
- A composite index contains more than one key column.
- Composite indexes can provide additional advantages over single-column indexes:
Improved selectivity:Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.
Reduced I/O:If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.
- A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
- A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.
Example:Create a composite index with columns name and id.
CREATE NONCLUSTERED INDEX student_id
ON student (ID, name)
