Oracle:如何跳过子键范围进行索引范围扫描？

Question

我正在使用 Oracle 12c。

考虑一个名为 GRID_CELLS 的表，它存储 100,000 x 100,000 的网格单元格数据。列如下:

• X号
• Y数
• CELL_VALUE 号码

索引由(X,Y)组成，是主键。

由于是网格单元格数据，几乎所有的“单元格”都有记录。

如果查询是这样的:

select * from GRID_CELLS where X >= 100 and X <= 200 and Y >= 100 and Y <= 200

Oracle 将选择索引范围扫描，并按如下方式操作:

1. 从根节点开始遍历B树索引，找到第一个叶子节点
2. 一直扫描叶子节点，直到最后一个叶子节点通过rowid访问表行

也就是说，Oracle 读取叶节点大约 100,000 * 100 条记录，这将比下面的(假设的) Action 慢很多。

1. 从根开始遍历B树索引找到第一个叶子节点(>=100, >=100)
2. 扫描叶节点，直到找到Y值大于200的记录
3. 停止叶节点扫描，从根节点开始重新遍历B树索引，为下一个X
4. 重复 2 和 3

Oracle 可以这样吗？如果是这样，它怎么能被迫这样做呢？还是有其他最优解？

顺便说一句，我们不能使用分区。因此，分区不是解决方案。

更新

我已经根据下面的评论测试了 2-bitmap-index 配置。结果是积极的。比B树索引范围扫描快5~10倍。

但是，如果 Oracle 在范围扫描 B 树索引时跳过叶节点，那还是不错的。

Answer 1

位图索引是一种方法。当使用位图索引时，即使是星形变换也是最佳的。位图索引的问题在于，当您有许多不同的 X 和 Y 值时，它们很大且查询速度较慢。当表中存在的不同 X 和 Y 值的总数与行数相比较少时，它们是最好的。

---

还有一些额外的步骤可以加快查询速度:

您可以按 X 或 Y 或两者对表进行分区。在这种情况下，速度增益取决于 X 和 Y 值的分布以及您的查询是否选择了一系列值。

您可以在 X 和 Y 上创建函数索引，或者将 X 和 Y 连接成一个字符串，然后在该字符串上创建索引。那么你将只有一把 key 。这可能是最佳解决方案，因为它将执行复杂度降低了一个程度。

如果执行期间的磁盘 I/O 很高，则将此表转换为 IOT(索引组织表)会给您带来好处，但对于包含许多列的表通常就是这种情况。

---

在决定索引策略时，您必须始终牢记该表在生产环境中的读写比率。如果有高读计数(SELECT)和低写计数(INSERT)那么你需要把大部分处理放在写部分:分区表，创建功能索引等。如果有高写计数和低读计数和SELECT速度不是那么重要那么 X 和 Y 上的一个索引是最好的解决方案。