database - Oracle 索引勉强加速聚合计算

Question

我创建了一个包含两列 a 和 b 的表格。 a 列只是数字 1 到 1 亿。 b 列是介于 0 和 999 之间的随机整数。我想使用此表来检查索引如何改进计算。所以我检查了以下内容:

select count(*) from my_table where b = 332 

select avg(a) from my_table where b = 387

332 和 387 只是随机整数，我想确保它没有缓存任何东西，所以我切换了它。

然后我创建了一个索引:

create bitmap index myindx1 on my_table (b);
commit;

这使计数 (*) 从 14 秒减少到 75 毫秒，成功!

但 avg(a) 表现不佳。它实际上变得更糟，从 8 秒到 10 秒。我没有对此进行多次测试，根据计划它看起来是侥幸，但至少它似乎并没有像我预期的那样做得更好。

没有索引的解释计划如下所示:

带有索引的解释计划如下所示:

所以它看起来有点帮助，但平均数字真的比计算它们要昂贵得多吗？平均数字比进行全表扫描更昂贵？我认为该索引会将我的查询削减到原始成本的一小部分，而不仅仅是节省一点时间。我还能做些什么来加快此查询的速度吗？

谢谢。

Answer 1

问题在于您设置测试的方式 - 它不切实际并且对索引不利。

首先:您的表中只有两个整数列，因此每一行都非常小。因此，Oracle 可以将很多行放入每个数据库 block 中——比如每个 block 几 千 行。

其次:您随机创建了索引数据，其值介于 0 到 999 之间。

将这两个事实放在一起，我们能猜出什么？答案:几乎每个数据库 block 都将至少有一行具有列 B 的任何给定值。

因此，无论您寻找什么 B 的值，您最终都会一次一个地读取表中的每个 block (即:“顺序读取”)。

将其与不使用索引的计划(全表扫描)进行比较，其中 Oracle 仍将读取每个 block ，但它会一次读取多个 block (即“分散读取”)。

难怪您的索引没有帮助。

如果您想要更好的测试，请将 C 列添加到您的测试表中，它只是一个包含 200-300 个字符的字符串(例如，“XXXXXXXXX...”)。这会将每个 block 的行数减少到更实际的值，您应该会从索引中看到更好的 yield 。

最后注意:使用BITMAP 索引时要非常小心。它们在发生任何类型的 DML(插入、更新、删除)的表上几乎不可用!在使用之前阅读所有关于它们的信息!

更新

对此的澄清:

So it looks like it's helping a bit, but is it really that much more expensive to average numbers than count them? And way more expensive to average numbers than to do a full table scan?

您的索引帮助您的 COUNT(*) 查询的原因是索引本身会告诉 Oracle 有多少行满足条件 B=332，所以它确实如此不需要读取表 block ，因此不会遇到我上面描述的问题(即逐个读取每个表 block )。

并不是说 COUNT() 比 AVG()“快”。只是，在您的测试中，COUNT 可以仅使用索引来计算，而 AVG 需要来自表的信息。