适用于范围查询条件的Oracle复合索引

Question

我有一张 table Blah(纬度浮点数，经度浮点数，create_time日期，owner_id int，.....)

我的代码只执行一个查询

select * 
from Blah 
where latitude < l1 and latitude > l2   
and longitude < ll1 and longitude > ll2   
and create_time < t1 and create_time > t2 
and owner_id < o1 and owner_id > o2 ;

(当然，值l1，l2，..... o1，o2是来自程序的动态参数)

我的问题是应该创建哪种索引？综合指数？
如果是复合索引，我应该放在哪一列？
索引的有效性如何？

我考虑了很长时间，找不到关于oracle索引如何工作的详细文档。

在我们的例子中，我可以找到使用B树实现的文档:B树中的每个键都是一个4元组:( column1，column2，column3，column4)，其中这些元组的排序关系定义为词法命令。

然后对于上面的查询，假设我们的订单是(owner_id，create_time，纬度，经度)，我猜
oracle首先需要二进制搜索到点(o1，t1，l1，ll1)，对于此操作，索引确实有用。
但是接下来，我们需要找到第一个中间对象的终点:我们需要找到(o1，t1，l1，ll2)，这也可以通过二进制搜索来完成。

接下来，我们需要找到满足条件的下一部分，因此我们需要
找到(o1，t1，lx，ll1)其中lx是下一个大于l1的值，我们也可以通过二进制搜索找到它。
但是在我们的例子中，很可能对于相同的纬度，经度不能超过1，
因此，二进制搜索在此并不比线性扫描更有效。

遵循这种精神，似乎我们应该将具有较小值范围基数的列放在首位，
在这种情况下，如果我们的积分仅在几天内创建，则为create_time。
另外，如果我们从不执行范围条件，而仅等于(=)条件，那么哪一列在第一列并不重要，对吧？

为了使它更清楚，这是一个更简单的示例:

假设我有2列X和Y

在数据库中，两者的值均为[1,2，.... 100]，因此我们有100x100行

我的查询是

select * from mytable where X > 34 and X < 78 and Y > 12 and Y < 15;

说我们的索引在(X，Y)上，所以两个值之间的比较规则是

v1 < v2 <=====>  v1.x < v2.x || v1.x == v2.x && v1.y < v2.y

给定上面的排序规则，我们可以看到索引中的值是
以串行方式排列(x，y的值):

1,1, 1,2 1,3 .... 1,100     
2,1  2,2 2,3 ......2,100
.....
100,1 100,2 ....... 100,100

现在，要在查询中搜索值，B树遍历需要
定位(78-34-1)间隔，因此(78-34-1)* 2查找(开始时为1
一个用于结束位置)，而不仅仅是2个查询。

因此，如果我们具有更大的尺寸，则间隔计数将呈指数增长
尺寸的数量，所以索引可能不再有用------
这是我的关注

多谢
杨

Answer 1

如果您唯一的目标是创建索引以优化此查询，则您最好将复合索引中的列排序为最有选择性的列。如果latitude上的谓词比其他谓词消除了更多的行，那么首先拥有该列将更加有效。如果owner_id上的谓词比其他谓词消除了更多的行，那么首先拥有该列将更加有效。

但是，实际上，我们很少创建仅用于优化单个查询的索引。通常，为了使索引维护的开销值得，我们希望索引在许多查询中有用。对于复合索引，这意味着按查询在该列上具有谓词的概率对列进行排序。例如，如果您在owner_id, create_time, latitude, longitude上有一个复合索引，则可以将其用于只在owner_id上指定谓词的查询。但是，实际上，您不会将该索引用于仅在longitude上指定谓词的查询。