Convincing the Oracle SQL optimizer that an indexed (though non-UNIQUE) column actually contains unique values in practice

Question

I am writing a view that uses a column with a non-UNIQUE index on it. However, within the context of my view, I am confident that the column will only contain unique values (due to the conditions imposed in the WHERE clause).

The real problem happens when someone queries the view based on that column (e.g. SELECT * FROM MY_VIEW WHERE COLUMN_WITH_NON_UNIQUE_INDEX = 'foo'). The optimizer is convinced that it will be receiving many rows (because the index is not technically UNIQUE). Because of this, the optimizer avoids using other indexes elsewhere in the view in favor of full table scans (not cool).

Is there a way to convince the the optimizer that a column with a non-UNIQUE index will, in fact, contain unique values? Sure, there is the possibility that duplicate values could sneak their way into the column, but it would be considered a bug and should not cause the legitimate, unique data to suffer.

Unfortunately, I am not in control of the table in question (sigh).

Answer 1

Oracle allows you to create unique (and primary key) constraints on views that are not enforced but that provide the optimizer with exactly this sort of information

ALTER VIEW your_view_name
  ADD CONSTRAINT name_of_constraint UNIQUE( column_with_non_unique_index )
  RELY DISABLE NOVALIDATE;

This will tell Oracle that it can rely on the fact that the data is unique but that it does not need to validate the constraint. The optimizer will, however, be able to use the additional metadata that the constraint provides.

Answer 2

You could try the following:

Gather statistics on the table with dbms_stats with METHOD_OPT set to FOR ALL COLUMNS SIZE AUTO With this setting, Oracle automatically determines which columns require histograms and the number of buckets (size) of each histogram. You can also manually specify which columns should have histograms and the size of each histogram.

dbms_stats.gather_table_stats(
ownname => 'schemaname' ,
tabname => 'tablename' ,
estimate_percent => 100 ,
method_opt => 'for all indexed columns size auto' ,
 cascade => true);