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).

Are your stats up do date? It should already be using the appropriate index if they are and it is indeed advantageous. — Mat, Oct 16 '12 at 11:23
Unique Indexes / Unique Constraints tell the optimiser what you want to tell it. Why would you expect that there is yet another way to do this? — MatBailie, Oct 16 '12 at 11:40
@Mat: The stats were stale so I gathered stats on the table. It improved some parts of the explain plan, but not this particular part. I will keep investigating. — Adam Paynter, Oct 16 '12 at 11:47
@Dems: After seeing all the hints available for the optimizer, I suspected that there may be *something* that can suggest this information. — Adam Paynter, Oct 16 '12 at 11:49
If you want to force it, try with a plain /*+ index (...) */ hint and _measure actual query time_ (not just the explain plan). — Mat, Oct 16 '12 at 11:57
Additional comment: you need to make sure you gather stats on the table and the indexes. — Mat, Oct 16 '12 at 12:17

score 2 · Accepted Answer · answered Oct 16 '12 at 14:41

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.

score 1 · Answer 2 · answered Oct 16 '12 at 12:24

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);

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

2 Answers2