Oracle 11g：在“select distinct”查询中未使用索引

Question

我的问题涉及Oracle 11g和在SQL查询中使用索引。在我的数据库中，有一张表结构如下：

Table tab (
  rowid NUMBER(11),
  unique_id_string VARCHAR2(2000),
  year NUMBER(4),
  dynamic_col_1 NUMBER(11),
  dynamic_col_1_text NVARCHAR2(2000)
 ) TABLESPACE tabspace_data;

我已经创建了两个索引：

CREATE INDEX Index_dyn_col1 ON tab (dynamic_col_1, dynamic_col_1_text) TABLESPACE tabspace_index;
CREATE INDEX Index_unique_id_year ON tab (unique_id_string, year) TABLESPACE tabspace_index;

包含大约1到2百万条记录。我通过执行以下SQL命令从中提取数据：

SELECT distinct
 "sub_select"."dynamic_col_1" "AS_dynamic_col_1","sub_select"."dynamic_col_1_text" "AS_dynamic_col_1_text"
FROM 
(
    SELECT "tab".*  FROM "tab"
    where "tab".year = 2011
) "sub_select"

很遗憾，尽管我创建了上述两个索引，但查询需要约1小时才能执行。解释计划显示Oracle使用了“表全访问”，即全表扫描。为什么没有使用索引？

作为实验，我测试了以下SQL命令：

SELECT DISTINCT
 "dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text"
 FROM "tab"

即使在这种情况下，索引也没有被使用，会执行完整的表扫描。

在我的真实数据库中，表包含更多的索引列，如“dynamic_col_1”和“dynamic_col_1_text”。整个索引文件的大小约为50GB。

一些更多的信息：

- 数据库是安装在我的本地计算机上的Oracle 11g。 - 我使用的是Windows 7 Enterprise 64位。 - 整个索引分为3个dbf文件，大小约为50GB。

如果有人能告诉我如何使Oracle在第一个查询中使用索引，我将不胜感激。因为第一个查询被另一个程序用于从数据库中提取数据，很难进行更改。因此，调整表格会很好。

提前致谢。

[01.10.2011：更新]

我认为我已经找到了这个问题的解决方案。两列dynamic_col_1和dynamic_col_1_text都是可空的。在更改表以禁止这两列的“NULL”值，并仅为列year添加一个新索引后，Oracle执行快速索引扫描。优点是查询现在只需要大约5秒钟就可以执行，而不是之前的1小时。

- oracle_user54

---

rowid 字段是主键吗？ - Bogdan Sahlean

请不要将表中的列命名为rowid，如果您需要使用实际的伪列rowid，那么您将会遇到很多麻烦。伪列rowid是数据库中的一个特殊列，如果您在表中使用了相同的名称，可能会导致混淆和错误。 - Ben

@BogdanSahlean 是的，rowid 是主键。 - oracle_user54

@Ben 我知道那是个糟糕的名字 ;-). 我的数据库中的列有其他名称。我只是以那种方式命名列，以指示主键所在的位置。 - oracle_user54

Answer 1

你确定使用索引访问比全表扫描更快吗？粗略估计，全表扫描比读取索引快20倍。如果“tab”中2011年的数据超过5％，那么Oracle使用全表扫描并不奇怪。正如@Dan和@Ollie所提到的，由于“year”是第二列，这将使索引变得更慢。

如果索引确实更快，则问题可能是错误的统计信息。有数百种统计信息可能会出错。非常简单地说，以下是我首先要查看的内容：

1. 使用索引提示运行解释计划和不使用索引提示运行解释计划。基数是否相差10倍或更多？时间是否相差10倍或更多？
2. 如果基数有误，请确保表和索引上有最新的统计信息，并且您正在使用合理的ESTIMATE_PERCENT（对于11g来说，DBMS_STATS.AUTO_SAMPLE_SIZE几乎总是最好的）。
3. 如果时间有误，请检查工作负载统计信息。
4. 您是否使用并行处理？Oracle始终假定并行处理具有近线性的改进效果，但在只有一个硬盘的桌面上，您可能根本看不到任何改进。

此外，这与您的问题无关，但您可能希望避免使用带引号的标识符。一旦使用它们，您必须在任何地方都使用它们，并且通常使您的表和查询难以处理。

- Jon Heller

---

过时的统计数据是许多性能问题的根本原因。对于 OP 即将访问的记录百分比的估计非常准确，这是一个不错的决定。如果他要检索表中相当比例的记录，全文搜索可能实际上是最高效的访问方法。 - Ollie

@Ollie 嗯，如果我错了，请纠正我，但我认为在“dynamic_col_1和dynamic_col_1_text”两列上使用索引会加快“select distinct dynamic_col_1，dynamic_col_1_text ...”查询的速度。不幸的是，我几乎无法更改SQL查询，因为它是由另一个工具生成以从表中提取数据。所以，这些带引号的标识符是不可避免的。 - oracle_user54

@oracle_user54，Ollie和jonearles想要表达的是索引并不总是比全表扫描更快。如果你要检索的行数百分比足够大，全表扫描会更快，因为（粗略地说）来回访问索引的开销大于不扫描其余行所获得的速度提升。 - Dan

Answer 2

您的索引应该是:

CREATE INDEX Index_year 
ON tab (year) 
TABLESPACE tabspace_index;

此外，您的查询可能只是以下内容：

SELECT DISTINCT
       dynamic_col_1 "AS_dynamic_col_1",
       dynamic_col_1_text "AS_dynamic_col_1_text"
  FROM tab
 WHERE year = 2011;

如果你的索引仅为此查询而创建，那么你可以将两个获取列包括在内创建索引，这样优化器就无需从表中获取查询数据，而是可以直接从索引中检索，使你的查询效率再次提高。

希望对你有所帮助...

- Ollie

---

假设@jonearles是错误的，索引会有帮助，他使用了你的查询（我会这样做）。最好的索引应该是year，dynamic_col_1，dynamic_col_1_text，因为他不需要通过rowid（实际的rowid）重新访问表。他可以直接从索引中获取所有数据。 - Ben

@Ollie 我已经考虑过了。但是如果我完全从查询中删除列 year 并执行 SELECT DISTINCT "dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text" FROM "tab"，索引也不会被使用。 - oracle_user54

@oracle_user54，查询语句大约获取了总表记录的百分之几？ - Ollie

@Ben 我可以明天或周二查看统计数据。需要有500个列是因为我从另一个程序中导入数据表，该程序要求这种结构。所以，我必须对表格或索引进行更改，因为获取数据的查询是自动生成的。 - oracle_user54

@Ollie 这个表格包含了1到2百万条记录。实际上，查询只会获取非常少量的数据，在大多数情况下不到5%。 - oracle_user54

Answer 3

我手头没有Oracle实例，所以这有些猜测，但我的倾向是说这是因为你将复合索引放错了顺序。如果你把year作为索引的第一列，它可能会被使用。

- Dan

Answer 4

我不知道这是否相关，但我测试了以下查询：

SELECT DISTINCT
"dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text"
FROM "tab"
WHERE "dynamic_col_1" = 123 AND "dynamic_col_1_text" = 'abc'

该查询的解释计划显示Oracle在此场景中使用索引扫描。

列dynamic_col_1和dynamic_col_1_text可为空。这会影响索引的使用吗？

01.10.2011:更新]

我认为我已经找到了问题的解决方案。将表更改为禁止两个列中的“NULL”值，并仅为年份列添加一个新索引后，Oracle执行快速索引扫描。优点是查询现在只需要大约5秒钟就可以执行，而不是之前的1小时。

- oracle_user54

Answer 5

你的第二个测试查询：

SELECT DISTINCT
"dynamic_col_1" "AS_dynamic_col_1", "dynamic_col_1_text" "AS_dynamic_col_1_text"
FROM "tab"

如果没有WHERE子句，就不要使用索引，因为这样会要求Oracle读取表中的每一行。在这种情况下，全表扫描是更快的访问方法。

此外，正如其他帖子所提到的，您的YEAR索引位于第二列。Oracle可以通过执行跳过扫描来使用此索引，但这样做会影响性能，并且根据您的表的大小，Oracle可能会再次决定使用FTS。

- Bart K

Answer 6

试试这个：

1）在年份字段上创建一个索引（参见Ollie的答案）。

2）然后使用这个查询：

SELECT DISTINCT
dynamic_col_1
,dynamic_col_1_text
FROM tab 
WHERE ID (SELECT ID FROM tab WHERE year=2011)

或者

SELECT DISTINCT
dynamic_col_1
,dynamic_col_1_text
FROM tab 
WHERE ID (SELECT ID FROM tab WHERE year=2011)
GROUP BY dynamic_col_1, dynamic_col_1_text

或许这会对你有所帮助。

- Bogdan Sahlean