oracle - 使用 Oracle 进行第一个字符计数导航 : count queries dont't use functional indices

Question

Oracle 9i。我们有一个大表(约 1M 行)，其中包含包含标题和作者列的内容。我们希望编写为该内容的标题和作者提供 A-Z 导航的 View (A:1300、B:45000，...)

首先进行一些没有任何索引的准备工作:

select * from content where substr(upper(title),0,1) = 'M'

表现略好于

select * from content where upper(title) LIKE 'M%'

具体计划:

TABLE ACCESS content FULL Cost=1624

即使没有任何索引，两者也相当快。现在是缓慢的部分:

select count(*) from CONTENT WHERE substr(upper(TITLE),0,1) = 'A';

具体计划:

SORT AGGREGATE else like above.

现在是累积(这就是我们想要的，它真的很慢):

select substr(upper(title),0,1) , count(*) from content group by substr(upper(title),0,1);

具体计划:

SORT: GROUP BY COST=8069 / TABLE ACCESS on CONTENT FULL COST=1624

所以我开始创建一个功能索引:

create index CONTENT_TITLE_LETTER_IDX on CONTENT(substr(upper(TITLE),0,1));

这极大地加快了单字母计数查询的速度:

select count(*) from CONTENT WHERE substr(upper(TITLE),0,1) = 'A';

ExPlan(几乎实时响应):

SORT AGGREGATE COST=1 / INDEX CONTENT_TITLE_LETTER_IDX RANGE SCAN COST=1

但是基本上查询相同内容的累积查询不使用索引(它显示与上面相同的解释计划)。我尝试了提示:

select /*+ index(CONTENT CONTENT_TITLE_LETTER_IDX) */ substr(upper(title),0,1) , count(*) from content group by substr(upper(title),0,1);

但它仍然很慢。我认为这可能是由于无序索引造成的，但我猜想即使我围绕所有 26 个可能的字母运行一个循环，单个查询 ( = 'letter' ) 也会更快!

谁知道如何告诉 Oracle 使用该索引(或者除了创建单字符列或表之外的替代方法)？

Answer 1

再看看您的查询:

select substr(upper(title),0,1) , count(*) 
from content 
group by substr(upper(title),0,1)

请注意没有任何 where 子句。事实上，您告诉数据库引擎获取所有行并计算每个首字母有多少行。您不能跳过任何行，因为否则您无法计算它。我认为索引不容易存储此类信息，因此完整扫描是最快的。如果您要求输入特定字母，则对索引使用范围扫描可能是有意义的。

如果您经常需要此信息，请创建一个汇总表，该汇总表将由主表上的触发器进行更新。