使Oracle SQL优化器确信索引(虽然非唯一)列实际上包含唯一值。

Question

我正在编写一个视图，它使用带有非UNIQUE索引的列。但是，在我的观点中，我相信该列将只包含唯一的值(由于WHERE子句中施加的条件)。

当有人根据该列(例如SELECT * FROM MY_VIEW WHERE COLUMN_WITH_NON_UNIQUE_INDEX = 'foo')查询视图时，就会出现真正的问题。优化器确信它将接收许多行(因为索引在技术上不是UNIQUE)。因此，优化器避免在视图的其他地方使用其他索引来支持完整的表扫描(不是很酷)。

是否有一种方法可以使优化器相信具有非UNIQUE索引的列实际上包含唯一的值？当然，重复的值有可能潜入列中，但是它会被认为是一个错误，不应该导致合法的、唯一的数据受到影响。

不幸的是，我没有控制这张桌子(叹息)。

Answer 1

Oracle允许您在视图上创建唯一(和主键)约束，这些约束不是强制的，而是为优化器提供了精确的此类信息。

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

这将告诉Oracle，它可以依赖于数据是唯一的这一事实，但它不需要验证约束。然而，优化器将能够使用约束提供的附加元数据。

Answer 2

您可以尝试以下几种方法：

将dbms_stats设置为METHOD_OPT的表中的统计信息自动设置为“所有列的大小自动”时，Oracle会自动确定哪些列需要直方图和每个直方图的桶数(大小)。您还可以手动指定哪些列应该有直方图和每个直方图的大小。

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