SQL 为按字段分组创建索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1445071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating Indexes for Group By Fields?
提问by onejigtwojig
Do you need to create an index for fields of group by fields in an Oracle database?
您是否需要为 Oracle 数据库中的 group by 字段的字段创建索引？
For example:
例如：
select *
from some_table
where field_one is not null and field_two = ?
group by field_three, field_four, field_five
I was testing the indexes I created for the above and the only relevant index for this query is an index created for field_two. Other single-field or composite indexes created on any of the other fields will not be used for the above query. Does this sound correct?
我正在测试我为上面创建的索引，此查询的唯一相关索引是为 field_two 创建的索引。在任何其他字段上创建的其他单字段或复合索引将不会用于上述查询。这听起来正确吗？
采纳答案by Eric Petroelje
It could be correct, but that would depend on how much data you have. Typically I would create an index for the columns I was using in a GROUP BY, but in your case the optimizer may have decided that after using the field_two index that there wouldn't be enough data returned to justify using the other index for the GROUP BY.
这可能是正确的，但这取决于您拥有多少数据。通常，我会为我在 GROUP BY 中使用的列创建一个索引，但在您的情况下，优化器可能已经决定在使用 field_two 索引后没有足够的数据返回来证明使用其他索引为 GROUP经过。
回答by Quassnoi
No, this can be incorrect.
不，这可能是不正确的。
If you have a large table, Oraclecan prefer deriving the fields from the indexes rather than from the table, even there is no single index that covers all values.
如果您有一个大表，Oracle可以更喜欢从索引而不是从表中导出字段，即使没有单个索引涵盖所有值。
In the latest article in my blog:
在我博客的最新文章中：
, there is a query in which Oracledoes not use full table scan but rather joins two indexes to get the column values:
，有一个查询Oracle不使用全表扫描而是连接两个索引来获取列值：
SELECT l.id, l.value
FROM t_left l
WHERE NOT EXISTS
(
SELECT value
FROM t_right r
WHERE r.value = l.value
)
The plan is:
计划是：
SELECT STATEMENT
HASH JOIN ANTI
VIEW , 20090917_anti.index$_join$_001
HASH JOIN
INDEX FAST FULL SCAN, 20090917_anti.PK_LEFT_ID
INDEX FAST FULL SCAN, 20090917_anti.IX_LEFT_VALUE
INDEX FAST FULL SCAN, 20090917_anti.IX_RIGHT_VALUE
As you can see, there is no TABLE SCANon t_lefthere.
正如你所看到的，有没有TABLE SCAN在t_left这里。
Instead, Oracletakes the indexes on idand value, joins them on rowidand gets the (id, value)pairs from the join result.
相反，Oracle在idandvalue上rowid获取索引，将它们连接起来并(id, value)从连接结果中获取对。
Now, to your query:
现在，对于您的查询：
SELECT *
FROM some_table
WHERE field_one is not null and field_two = ?
GROUP BY
field_three, field_four, field_five
First, it will not compile, since you are selecting *from a table with a GROUP BYclause.
首先，它不会编译，因为您是*从带有GROUP BY子句的表中进行选择。
You need to replace *with expressions based on the grouping columns and aggregates of the non-grouping columns.
您需要*根据分组列和非分组列的聚合替换为表达式。
You will most probably benefit from the following index:
您很可能会从以下索引中受益：
CREATE INDEX ix_sometable_23451 ON some_table (field_two, field_three, field_four, field_five, field_one)
, since it will contain everything for both filtering on field_two, sorting on field_three, field_four, field_five(useful for GROUP BY) and making sure that field_oneis NOT NULL.
，因为它将包含过滤field_two、排序field_three, field_four, field_five（对 有用GROUP BY）和确保field_one是 的所有内容NOT NULL。
回答by Jeffrey Kemp
Do you need to create an index for fields of group by fields in an Oracle database?
您是否需要为 Oracle 数据库中的 group by 字段的字段创建索引？
No. You don't need to, in the sense that a query will run irrespective of whether any indexes exist or not. Indexes are provided to improve query performance.
不。您不需要，因为无论是否存在任何索引，查询都会运行。提供索引以提高查询性能。
It can, however, help; but I'd hesitate to add an index just to help one query, without thinking about the possible impact of the new index on the database.
但是，它可以提供帮助；但是我会犹豫是否添加索引只是为了帮助一个查询，而没有考虑新索引对数据库的可能影响。
...the only relevant index for this query is an index created for field_two. Other single-field or composite indexes created on any of the other fields will not be used for the above query. Does this sound correct?
...此查询的唯一相关索引是为 field_two 创建的索引。在任何其他字段上创建的其他单字段或复合索引将不会用于上述查询。这听起来正确吗？
Not always. Often a GROUP BY will require Oracle to perform a sort (but not always); and you can eliminate the sort operation by providing a suitable index on the column(s) to be sorted.
不总是。通常 GROUP BY 会要求 Oracle 执行排序（但并非总是如此）；您可以通过在要排序的列上提供合适的索引来消除排序操作。
Whether you actually need to worry about the GROUP BY performance, however, is an important question for you to think about.
然而，您是否真的需要担心 GROUP BY 的性能，这是您需要考虑的一个重要问题。
