What's The Difference Between Composite Index And Unique Index In Sql?


1 Answers

Naeem Sheeraz Profile
Naeem Sheeraz answered
It can be build on a single column or on multiple column. when creating index on single column called as single column index and creating on more than column called as composite column index. (there is not any avail term such as unique or composite index, only can be define in constraints, unique identified as " value will be unique in each record and cannot be nulll" , composite key mean any key (unique,primary,foreign) which defined on combining more than one columns)

About Index:
Indexes are optional data structures built on tables. Indexes can improve data retrieval performance by providing a direct access method instead of the default full table scan retrieval method. You can build Btree or bitmap indexes on one or more columns in a table.

An index
key is defined as one data value stored in the index. A Btree index sorts the keys into a
binary tree and stores these keys together with the table's ROWIDs. In a bitmap index,
a bitmap is created for each key. There is a bit in each bitmap for every ROWID in the table,
forming the equivalent of a two-dimensional matrix. The bits are set if the corresponding
row in the bitmap exists.

Btree indexes are the default index type, can be unique or non-unique, and are appropriate for medium- to high-cardinality columns—those having many distinct values. Btree indexes support row-level locking and so are appropriate for multi-user, transactional applications. The indexes supporting a PRIMARY KEY or UNIQUE constraints are Btree indexes.

Bitmap indexes, on the other hand, are best for multiple combinations of low- to medium cardinality columns (you cannot create a unique bitmap index), and they do not support row level locking. Bitmap indexes are best in environments in which changes to data are limited and controlled, such as many data warehousing applications. Because bitmap indexes cannot efficiently make changes to the indexed data, they are often dropped prior to data loading and then re-created after a data load.

Answer Question