oracle 如何使用 group by 语句 + max 函数加速 SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1739068/
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
How to speed up SQL query with group by statement + max function?
提问by tputkonen
I have a table with millions of rows, and I need to do LOTS of queries which look something like:
我有一个包含数百万行的表，我需要执行许多类似于以下内容的查询：
select max(date_field)
where varchar_field1 = 'something'
group by varchar_field2;
My questions are:
我的问题是：
- Is there a way to create an index to help with this query?
- What (other) options do I have to enhance performance of this query?
- 有没有办法创建一个索引来帮助这个查询？
- 我必须使用哪些（其他）选项来增强此查询的性能？
回答by Andomar
An index on (varchar_field1, varchar_field2, date_field)would be of most use. The database can use the first index field for the whereclause, the second for the group by, and the third to calculate the maximum date. It can complete the entire query just using that index, without looking up rows in the table.
索引(varchar_field1, varchar_field2, date_field)将是最有用的。数据库可以将第一个索引字段用于where子句，第二个用于group by，第三个用于计算最大日期。它只需使用该索引即可完成整个查询，而无需查找表中的行。
回答by Dmitry
Obviously, an index on varchar_field1 will help a lot.
显然， varchar_field1 上的索引会有很大帮助。
回答by O. Jones
You can create yourself an extra table with the columns
您可以为自己创建一个带有列的额外表
varchar_field1 (unique index)
max_date_field
You can set up triggers on inserts, updates, and deletes on the table you're searching that will maintain this little table -- whenever a row is added or changed, set a row in this table.
您可以在您正在搜索的表上设置插入、更新和删除触发器，以维护这个小表——每当添加或更改一行时，在该表中设置一行。
We've had good success with performance improvement using this refactoring technique. In our case it was made simpler because we never delete rows from the table until they're so old that nobody ever looks up the max field. This is an especially helpful technique if you can add max_date_fieldto some other table rather than create a new one.
我们使用这种重构技术在性能改进方面取得了巨大成功。在我们的例子中，它变得更简单，因为我们永远不会从表中删除行，直到它们太旧以至于没人会查找 max 字段。如果您可以添加max_date_field到其他表而不是创建新表，则这是一种特别有用的技术。
