We all know that, a unique constraint nominates a column (or combination of columns) for which the value must be different for every row in the table. If based on a single column, this is known as the key column. If the constraint is composed of more than one column (known as a composite key unique constraint) the columns do not have to be the same data type or be adjacent in the table definition.

An oddity of unique constraints is that it is possible to enter a NULL value into the key column(s); it is indeed possible to have any number of rows with NULL values in their key column(s). So selecting rows on a key column will guarantee that only one row is returned—unless you search for NULL, in which case all the rows where the key columns are NULL will be returned.

Unique constraints are enforced by an index. When a unique constraint is defined, Oracle will look for an index on the key column(s), and if one does not exist it will be created. Then whenever a row is inserted, Oracle will search the index to see if the values of the key columns are already present: if they are, it will reject the insert.

The structure of these indexes (known as B*Tree indexes) does not include NULL values, which is why many rows with NULL are permitted: they simply do not exist in the index. While the first purpose of the index is to enforce the constraint, it has a secondary effect: improving performance if the key columns are used in the WHERE clauses of SQL statements. However, selecting WHERE key_column IS NULL cannot use the index because it doesn’t include the NULLs and will therefore always result in a scan of the entire table.

Read more on Oracle - B-Tree Index and How b-tree database indexes work and how to tell if they are efficient