Oracle SQL - 检查 SQL 查询中缺失的索引

Question

我需要检查查询中是否缺少索引。

要检查表中的索引，我使用以下语法：

SELECT index_name
     , column_position
     , column_name
  FROM user_ind_columns
 WHERE table_name = 'table_name'
 ORDER BY index_name, COLUMN_POSITION;

 SELECT index_name 
 FROM user_indexes
 WHERE table_name = 'table_name';

有没有可能使用程序或SQL脚本自动查找查询中是否缺少索引，并显示它们以便创建？

谢谢！

- Catalin

---

你在谈论 EXPLAIN PLAN 吗？ - Andrey Khmelev

Answer 1

并不完全如此。查看执行计划可以告诉您优化器将使用哪些索引（如果有的话），但如果计划中没有显示索引，则可能是以下情况之一：

1. 没有索引。
2. 存在索引，但不能用于特定查询。
3. 存在可用的索引，但优化器计算出它们不会提高性能。

在第三种情况下，它总是可能是错误的（例如由于不正确的统计信息或超出优化器准确建模的能力）。如果这是您要查找的情况，则没有简单的方法来检测它。

您可以查看dbms_sqltune以获取自动调整建议。通常它会告诉您收集统计信息，有时会建议SQL概要文件或新索引。您可以从企业管理器或脚本中调用它：

http://www.williamrobertson.net/documents/automated-sql-tuning-advice.html http://www.williamrobertson.net/documents/automated-sql-tuning-advice-sqlid.html

- William Robertson

---

嗨，我在下面添加了答案。 - mattsmith5

有时候全表扫描实际上是最好的方案。索引并不是万能药。 - The Impaler

Answer 2

使用Oracle Autonomous Database，您可以启用自动索引。自动索引可自动化索引管理任务，无需对基于工作负载模式适当创建、维护和废弃/不使用索引的知识和专业技能。

启用自动索引

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

禁用自动索引

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

请参考Oracle文档中的自动索引章节以获取更多详细信息。

- RaviSharma-Oracle

---

听起来是一个有趣的选项。我想了解一下建议新索引的准确性如何。 - The Impaler

给了赏金和积分，嗨？下面的回答也有效果啊：）？可没人喜欢呢。 - mattsmith5

Answer 3

这是一个不错的资源：https://www.dba-scripts.com/scripts/diagnostic-and-tuning/troubleshooting/find-missing-index/

ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:'

select * from (
  select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index
  from sys.col_usage$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.user$ us
  where u.obj# = o.obj#
  and   u.obj# = c.obj#
  and   us.user# = o.owner#
  and   u.intcol# = c.col#
  and   us.name='&SCHEMA_NAME'
  and   c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME')
  and   (u.equality_preds > 100 OR u.equijoin_preds > 100)
  order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;

- mattsmith5

Answer 4

我发现一个可靠的索引建议方法（以及许多其他改进建议）是在OEM（Oracle Enterprise Manager）中使用SQL调整顾问。它有时会为您的语句找到更好的计划，并将其添加到SQL配置文件中，您可以在dba_sql_profiles下检查。

SQL Advisor将以以下格式提供索引建议：

Consider running the Access Advisor to improve the physical schema design or creating the recommended index.

<schema.table>(field list)

Creating the recommended indices significantly improves the execution plan of this statement.

您需要先运行SQL语句，然后在OEM实例的“顶部活动”下找到它，或通过SQL ID进行搜索。

- sandman