Skip to main content

​
Background: What are unique constraints?

When the optimizer knows that certain columns contain only unique values, it can make better estimates about the number of rows in certain situations, then leading to better plans. You can learn more about how unique constraints improve performance in the documentation for UNIQUE constraints at the table level.

​
Using the unique columns hint

In contrast to specifying a unique constraint in the table definition, the unique hint allows you to specify uniqueness for a single query, without modifying the table definition. This enables testing the performance impact of unique constraints before adding them to your table schema. The hint has the same effect as declaring a column UNIQUE when creating a table, but applies only to the current query. To make sure that all queries can benefit from the hint, we still recommend declaring the column as UNIQUE in the table definition.
Since unique constraints are not enforced, ensure your data actually satisfies the uniqueness assumption. Incorrect hints can lead to incorrect query results.

​
Example

/*! unique(customers.customer_id) */
select c.customer_id, c.name, o.total_amount
from customers c
join orders o on c.customer_id = o.customer_id
⌘I