索引可以与oracle中的group函数一起使用吗？

Question

我正在运行以下查询。

SELECT Table_1.Field_1,
           Table_1.Field_2,
           SUM(Table_1.Field_5) BALANCE_AMOUNT
      FROM Table_1, Table_2
     WHERE Table_1.Field_3 NOT IN (1, 3)
       AND Table_2.Field_2 <> 2
       AND Table_2.Field_3 = 'Y'
       AND Table_1.Field_1 = Table_2.Field_1
       AND Table_1.Field_4 = '31-oct-2011'
     GROUP BY Table_1.Field_1, Table_1.Field_2;

我已为 Table_1 的列 (Field_1,Field_2,Field_3,Field_4) 创建索引，但该索引未使用。

如果我从 select 子句中删除 SUM(Table_1.Field_5) ，则将使用索引。

我很困惑优化器是否没有使用该索引或其因为我在查询中使用的 SUM() 函数。

请分享您对此的解释。

Answer 1

当您删除 SUM 时，您还会从查询中删除 field_5。然后可以在索引中找到回答查询所需的所有数据，这可能比扫描表更快。如果您将 field_5 添加到索引，则使用 SUM 的查询可能会使用该索引。

Answer 2

如果您的查询返回大部分表行，Oracle 可能会认为执行全表扫描比在索引和表堆之间“跳跃”（以获取 Table_1.Field_5 中的值）更便宜）。

尝试将 Table_1.Field_5 添加到索引（从而用索引覆盖整个查询），看看这是否有帮助。

请参阅仅索引扫描：避免表访问< /a> 使用卢克索引对正在发生的事情进行概念性解释。

Answer 3

正如您所提到的，求和函数的存在会导致索引被忽略。

有基于函数的索引：

基于函数的索引包括由函数（例如 UPPER 函数）转换的列，或包含在表达式（例如 col1 + col2）中的列。

在转换后的列或表达式上定义基于函数的索引允许在 WHERE 子句或 ORDER BY 子句中使用该函数或表达式时使用该索引返回数据。因此，当频繁执行的 SQL 语句在 WHERE 或 ORDER BY 子句中包含转换列或表达式中的列时，基于函数的索引可能会很有用。

然而，与所有基于函数的索引有他们的限制：

基于函数的索引中的表达式不能包含任何聚合函数。表达式必须仅引用表中一行中的列。

Answer 4

虽然我在这里看到了一些很好的答案，但遗漏了几个重要的点 -

SELECT Table_1.Field_1,
           Table_1.Field_2,
           SUM(Table_1.Field_5) BALANCE_AMOUNT
      FROM Table_1, Table_2
     WHERE Table_1.Field_3 NOT IN (1, 3)
       AND Table_2.Field_2 <> 2
       AND Table_2.Field_3 = 'Y'
       AND Table_1.Field_1 = Table_2.Field_1
       AND Table_1.Field_4 = '31-oct-2011'
     GROUP BY Table_1.Field_1, Table_1.Field_2;

说在 select 子句中使用 SUM(Table_1.Field_5) 会导致索引不被正确使用。您在 (Field_1,Field_2,Field_3,Field_4) 上的索引仍然可以使用。但是你的索引和sql查询有问题。

由于您的索引仅位于 (Field_1,Field_2,Field_3,Field_4) 上，即使您的索引被使用，数据库也必须访问实际的表行以获取 Field_5 来应用过滤器。现在它完全取决于sql优化器制定的执行计划哪一个是具有成本效益的。如果 SQL 优化器发现全表扫描的成本低于使用索引的成本，它将忽略索引。这么说，我现在将告诉您索引可能存在的问题 -

1. 正如其他人所说，您可以简单地将 Field_5 添加到索引，这样就不需要单独的表访问。
2. 索引的顺序对于性能非常重要。例如。在您的情况下，如果您将订单指定为 (Field_4,Field_1,Field_2,Field_3) 那么它会更快，因为您在 Field_4 -Table_1.Field_4 = '31-oct-2011' 上具有相等性。想想看，这是 -

Table_1.Field_4 = '31-oct-2011' 将为您提供更少的选项来选择最终结果 Table_1.Field_3 NOT IN (1, 3)代码>.由于您正在进行联接，情况可能会发生变化。最好查看执行计划并相应地设计索引/sql。