performance - Oracle:使用动态值的基于函数的索引

Question

我有一个复杂的 SQL 查询。查询的一个简单部分如下所示:

Query 1:
SELECT *
  FROM table1 t1, table2 t2
 WHERE t1.number       = t2.number
   AND UPPER(t1.name)  = UPPER(t2.name)
   AND t1.prefix       = p_in_prefix;


Query 2:
SELECT *
  FROM table1 t1, table2 t2
 WHERE t1.number       = t2.number
   AND UPPER(t1.name)  = UPPER(p_in_prefix || t2.name)
   AND t1.prefix       = p_in_prefix;

我在 table1 上有基于函数的索引作为(数字，UPPER(名称))。我的 table2 上有基于函数的索引(数字，UPPER(NAME))。 p_in_prefix 是一个输入参数(基本上是一个数字)。

由于这些索引，我的查询 1 运行高效。但是查询 2 存在性能问题，因为在查询 2 中，'t2.name' 以 p_in_prefix 为前缀。

我无法为查询 2 创建基于函数的索引，因为 p_in_prefix 是一个输入参数，我在创建索引时不知道它可能包含什么值。如何解决这种情况下的性能问题？任何提示/想法将不胜感激。如果您需要更多信息，请告诉我。谢谢。

Answer 1

使用 AND UPPER(t1.name) = UPPER(p_in_prefix) || UPPER(t2.name).

因为你有一个基于函数的索引作为 table2 的 UPPER(NAME)，你应该在查询中有一个具有相同表达式的操作数，以便使用基于函数的索引。

使用 UPPER(p_in_prefix || t2.name) 将不会使用基于函数的索引，因为它与函数表达式 UPPER(NAME) 不匹配。请注意，使用 UPPER(t2.name) 不会导致任何问题，因为 t2 只是一个列别名。

除此之外，您还可以在查询中传递优化器提示，以指示优化器使用索引。

有关更多信息，请阅读 Jason Price 撰写的“Oracle 数据库 11g SQL”。

另请阅读 Oracle 文档 here和 here和优化器提示 here .