Understanding Partial Uniqueness in Oracle

In relational database design, a common requirement is enforcing a partial unique constraint—for example, ensuring a customer can have at most one active order at any given time (where end_ts IS NULL), while allowing infinitely many closed orders. In Oracle, standard unique constraints don't support conditional logic directly.

Developers frequently turn to Function-Based Indexes (FBIs) or Virtual Columns to solve this. Because standard B-Tree indexes in Oracle do not store entries where all indexed keys are NULL, returning NULL for non-active records effectively excludes them from the unique index.

CREATE UNIQUE INDEX unique_active_order 
ON orders (CASE WHEN end_ts IS NULL THEN customer_id END);

The Query Matching Challenge

A common point of confusion arises when developers try to reuse this specific unique index for general queries or table joins. Oracle's Cost-Based Optimizer (CBO) requires that the exact expression used in the function-based index definition appears in the query's WHERE or SELECT clause for the index to be considered.

If you attempt to join the orders table to another table like task on order_id for open orders, you might wonder if you need to wrap the join condition in a CASE statement or create another index entirely.

Do You Need Another Index?

The short answer is: No, you do not need another function-based index to join these tables efficiently.

It is crucial to distinguish between an index built for data integrity (uniqueness) and an index built for query execution/joins:

  • The FBI on customer_id guarantees that no customer has duplicate active orders.
  • The Primary Key on orders.id already indexes every order uniquely.
  • An Index on task.order_id facilitates fast lookup from tasks back to orders.

Writing Natural SQL for Joins

Instead of forcing complex CASE expressions inside your JOIN clause, write standard, clean SQL filters. The optimizer handles the execution path efficiently without needing identical CASE constructs in the join:

SELECT o.id, o.customer_id, t.id AS task_id
FROM orders o
LEFT OUTER JOIN task t 
  ON o.id = t.order_id
WHERE o.end_ts IS NULL;

In this query, Oracle will use the Primary Key index on orders.id (or perform a range/full scan depending on the active order volume) and join to task using the index on task.order_id. You do not need to alter the join condition to match the functional unique index.

Best Practice: Virtual Columns (Oracle 11g and Newer)

While direct Function-Based Indexes work well, using Virtual Columns is generally considered a cleaner, more maintainable approach in modern Oracle databases.

-- 1. Add a virtual column for the conditional logic
ALTER TABLE orders ADD active_customer_id AS 
  (CASE WHEN end_ts IS NULL THEN customer_id ELSE NULL END);

-- 2. Create a unique index on the virtual column
CREATE UNIQUE INDEX idx_unique_active_order 
ON orders (active_customer_id);

Benefits of the Virtual Column Approach:

  • Cleaner Queries: Developers can query WHERE active_customer_id IS NOT NULL directly without repeating SQL expressions.
  • Better Statistics: Oracle gathers histogram statistics directly on virtual columns, helping the optimizer make accurate cardinality estimates.
  • Separation of Concerns: Integrity constraints remain clean and readable, while standard primary and foreign keys handle relational joins.

Summary

Do not worry about over-indexing simply because a query filtering active orders cannot use your partial unique index. Unique function-based indexes are designed primarily to enforce business rules. For optimal join performance, rely on standard Foreign Key and Primary Key indexes paired with straightforward WHERE clause predicates.