What is composite index?

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)
What is a JOIN? Explain types of JOIN in oracle.
What is a JOIN? Explain types of JOIN in oracle - A join is a query that extracts data from two or more tables, views or snapshots.....
Explain various types of joins.
Explain various types of joins - Types of joins are: - Equijoins , Non-equijoins....
What is oracle Join?
What is oracle Join? - A join is a query that combines rows of two or more tables or views.....
Post your comment