I have 2 tables that are a few millions of rows with indexes. I'm looking to convert one of the indexes to DESC order to optimize some operations. However, will that affect joinining speed or other optimizations?
我有两个表，有几百万行的索引。我想把其中一个索引转换成DESC，以优化一些操作。但是，这会影响到连接速度或其他优化吗?
For example:
例如:
Table A:
a_id (pk)
表一:a_id(pk)
Table B:
b_id (pk)
a_id (fk)
表B: b_id (pk) a_id (fk)
If A.a_id is stored as DESC and B.a_id is stored ASC will I encounter any problems or slowness on joins? Will oracle be able to use the indexes for joining even though they have different sort orders? Do I have to make B.a_id DESC as well or create a second index that is DESC ? Obviously I'd like to try a simple experiment but I don't have DBA access or a spare oracle setup to work with.
如果一个。a_id存储为DESC和B。a_id被存储为ASC，我在连接上会遇到什么问题或缓慢吗?即使有不同的排序顺序，oracle也能使用这些索引来加入吗?我必须做B吗?a_id DESC还是创建第二个索引DESC ?显然，我想尝试一个简单的实验，但我没有DBA访问权限，也没有可用的oracle设置。
6 个解决方案
#1
4
Will oracle be able to use the indexes for joining even though they have different sort orders?
即使有不同的排序顺序，oracle也能使用这些索引来加入吗?
Indexes are not used "for joining". They're used to access data. The row sources thus created are then joined. The only reason I can think of that the sort order of the index would have any impact on joining would be if a merge join is occurring and the index is being used to avoid sorting. In this case, the impact of changing to a descending index might be that the data needs to be sorted in memory after it is accessed; or it might not, if the optimizer is intelligent enough to simply walk through that data in reverse order when doing the merge.
索引不用于“加入”。它们被用来访问数据。然后将由此创建的行源联接起来。我认为索引的排序顺序会对连接产生影响的唯一原因是，如果合并连接发生，并且索引被用于避免排序的话。在这种情况下，更改为下降索引的影响可能是数据在访问后需要在内存中排序;或者，如果优化器足够智能，在执行合并时只需以相反的顺序遍历数据，那么它可能不会。
If you have queries whose execution plans rely on using the index on A.A_ID to get the data in ascending order (either for purposes of a merge join or to meet your requested ordering of the results), then changing the index to descending order could have an impact.
如果查询的执行计划依赖于在A上使用索引。A_ID以升序(出于合并连接的目的或为了满足您对结果的请求排序)获取数据，然后将索引更改为降序可能会产生影响。
Edit: Just did a quick test on some sample data. The optimizer does seem to have the capability to merge row sources sorting in opposite orders without resorting either of them. So at the most obvious level, having one index ascending and the other descending should not cause serious performance problems. However, it does look like the descending indexes can have other effects on the execution plan -- in my case, the ascending index was used for a fast full scan, while the descending one was used for a range scan. This could cause changes in query performance -- good or bad -- but the only way to know for certain is to test it.
编辑:对一些示例数据做了一个快速测试。优化器似乎有能力将行源合并到相反的顺序中，而不用求助于它们中的任何一个。因此，在最明显的层次上，一个指标上升，另一个下降不应该导致严重的性能问题。但是，看起来降序索引对执行计划有其他影响——在我的例子中，升序索引用于快速的全扫描，而降序索引用于范围扫描。这可能会导致查询性能的变化(好或坏)，但确定的唯一方法是测试它。
#2
2
Oracle implements indexes as doubly-linked lists, so it makes no difference whether you specify an ASC or DESC index for a single column.
Oracle将索引作为doubly链接的列表实现，所以对于单个列指定ASC或DESC索引也没有什么区别。
DESC indexes are a special case that helps when you have a multi-column index, e.g. if I have a query that often orders by colA ASC, colB DESC, then I might decide to add an index on (colA, colB DESC) in order to avoid a sort.
DESC索引是一种特殊的情况，当您有一个多列索引时，它会很有帮助，例如，如果我有一个查询经常由colA ASC、colB DESC订购，那么我可能会决定在(colA, colB DESC)上添加一个索引，以避免排序。
#3
0
Developing without a development and test system? Your answer is to develop with one. Oracle comes on all platforms, just install, add data, do your work.
没有开发和测试系统的开发?你的答案是用一个。Oracle适用于所有平台，只需安装、添加数据、完成工作。
For you, just live dangerously and do the index change, who cares what happens. Grab for that brass ring. So you miss. You won't lose any data.
对你来说，只是过着危险的生活，改变指数，谁在乎发生了什么。抓住那个铜环。所以你不会丢失任何数据。
#4
0
I'm not sure I get what you're trying to ask - you cannot "store" in descending or ascending order. You can fetch the results of the query and order it using ORDER BY clause which will sort the resulting set in ascending or descending order.
我不确定我是否理解你想问的——你不能按降序或升序“存储”。您可以获取查询的结果并使用order BY子句对其排序，该子句将按升序或降序对结果集进行排序。
There is no guarantee that you're inserting any data in ascending or descending order.
不能保证插入的数据是按升序或降序排列的。
Consequently, the "order" by which it is inserted will have no bearing on the performance because there is no order
因此，插入它的“顺序”将不会影响性能，因为没有顺序
#5
0
Generally speaking an index can do scanning in asc/desc order since the 2 pointers in the index structure are sufficient to identify leaf blocks and corresponding blocks while doing scan based on asc/desc order without sorting in the memory.
一般来说，索引可以按照asc/desc顺序进行扫描，因为索引结构中的两个指针足以识别叶块和相应的块，同时基于asc/desc顺序进行扫描，而无需在内存中进行排序。
However if we create an index with desc column definition its structure will be much larger than the a normal index since the normal index has a 90-10 splits (incrementing row ids) where as desc index will be 50-50 splits and will lead to unused space and a candidate for rebuild which will require additional maintenance and overhead.
然而如果我们创建一个索引desc列定义其结构将远远大于正常指数正常指数90年10月以来分裂(递增行id)desc指数将一半分裂,导致未使用的空间和重建将需要额外的维护和候选人的开销。
DESC indexes can be helpful when you have a multi-column index where one column is need in asc while the other in desc to avoid sorting in the memory.
当有多列索引时，DESC索引是有用的，其中一个列在asc中需要，而另一个列在DESC中需要避免在内存中排序。
#6
-1
Early optimization is a waste of time. Just leave this problem and do the next thing. When there are 100 million rows in this table change the indexes and test what happens, until then your ten rows of data are not worth the time to "optimize".
早期的优化是浪费时间。把这个问题抛在一边，做下一件事。当这个表中有1亿个行更改索引并测试所发生的情况时，那么您的10行数据就不值得花时间去“优化”了。
#1
4
Will oracle be able to use the indexes for joining even though they have different sort orders?
即使有不同的排序顺序，oracle也能使用这些索引来加入吗?
Indexes are not used "for joining". They're used to access data. The row sources thus created are then joined. The only reason I can think of that the sort order of the index would have any impact on joining would be if a merge join is occurring and the index is being used to avoid sorting. In this case, the impact of changing to a descending index might be that the data needs to be sorted in memory after it is accessed; or it might not, if the optimizer is intelligent enough to simply walk through that data in reverse order when doing the merge.
索引不用于“加入”。它们被用来访问数据。然后将由此创建的行源联接起来。我认为索引的排序顺序会对连接产生影响的唯一原因是，如果合并连接发生，并且索引被用于避免排序的话。在这种情况下，更改为下降索引的影响可能是数据在访问后需要在内存中排序;或者，如果优化器足够智能，在执行合并时只需以相反的顺序遍历数据，那么它可能不会。
If you have queries whose execution plans rely on using the index on A.A_ID to get the data in ascending order (either for purposes of a merge join or to meet your requested ordering of the results), then changing the index to descending order could have an impact.
如果查询的执行计划依赖于在A上使用索引。A_ID以升序(出于合并连接的目的或为了满足您对结果的请求排序)获取数据，然后将索引更改为降序可能会产生影响。
Edit: Just did a quick test on some sample data. The optimizer does seem to have the capability to merge row sources sorting in opposite orders without resorting either of them. So at the most obvious level, having one index ascending and the other descending should not cause serious performance problems. However, it does look like the descending indexes can have other effects on the execution plan -- in my case, the ascending index was used for a fast full scan, while the descending one was used for a range scan. This could cause changes in query performance -- good or bad -- but the only way to know for certain is to test it.
编辑:对一些示例数据做了一个快速测试。优化器似乎有能力将行源合并到相反的顺序中，而不用求助于它们中的任何一个。因此，在最明显的层次上，一个指标上升，另一个下降不应该导致严重的性能问题。但是，看起来降序索引对执行计划有其他影响——在我的例子中，升序索引用于快速的全扫描，而降序索引用于范围扫描。这可能会导致查询性能的变化(好或坏)，但确定的唯一方法是测试它。
#2
2
Oracle implements indexes as doubly-linked lists, so it makes no difference whether you specify an ASC or DESC index for a single column.
Oracle将索引作为doubly链接的列表实现，所以对于单个列指定ASC或DESC索引也没有什么区别。
DESC indexes are a special case that helps when you have a multi-column index, e.g. if I have a query that often orders by colA ASC, colB DESC, then I might decide to add an index on (colA, colB DESC) in order to avoid a sort.
DESC索引是一种特殊的情况，当您有一个多列索引时，它会很有帮助，例如，如果我有一个查询经常由colA ASC、colB DESC订购，那么我可能会决定在(colA, colB DESC)上添加一个索引，以避免排序。
#3
0
Developing without a development and test system? Your answer is to develop with one. Oracle comes on all platforms, just install, add data, do your work.
没有开发和测试系统的开发?你的答案是用一个。Oracle适用于所有平台，只需安装、添加数据、完成工作。
For you, just live dangerously and do the index change, who cares what happens. Grab for that brass ring. So you miss. You won't lose any data.
对你来说，只是过着危险的生活，改变指数，谁在乎发生了什么。抓住那个铜环。所以你不会丢失任何数据。
#4
0
I'm not sure I get what you're trying to ask - you cannot "store" in descending or ascending order. You can fetch the results of the query and order it using ORDER BY clause which will sort the resulting set in ascending or descending order.
我不确定我是否理解你想问的——你不能按降序或升序“存储”。您可以获取查询的结果并使用order BY子句对其排序，该子句将按升序或降序对结果集进行排序。
There is no guarantee that you're inserting any data in ascending or descending order.
不能保证插入的数据是按升序或降序排列的。
Consequently, the "order" by which it is inserted will have no bearing on the performance because there is no order
因此，插入它的“顺序”将不会影响性能，因为没有顺序
#5
0
Generally speaking an index can do scanning in asc/desc order since the 2 pointers in the index structure are sufficient to identify leaf blocks and corresponding blocks while doing scan based on asc/desc order without sorting in the memory.
一般来说，索引可以按照asc/desc顺序进行扫描，因为索引结构中的两个指针足以识别叶块和相应的块，同时基于asc/desc顺序进行扫描，而无需在内存中进行排序。
However if we create an index with desc column definition its structure will be much larger than the a normal index since the normal index has a 90-10 splits (incrementing row ids) where as desc index will be 50-50 splits and will lead to unused space and a candidate for rebuild which will require additional maintenance and overhead.
然而如果我们创建一个索引desc列定义其结构将远远大于正常指数正常指数90年10月以来分裂(递增行id)desc指数将一半分裂,导致未使用的空间和重建将需要额外的维护和候选人的开销。
DESC indexes can be helpful when you have a multi-column index where one column is need in asc while the other in desc to avoid sorting in the memory.
当有多列索引时，DESC索引是有用的，其中一个列在asc中需要，而另一个列在DESC中需要避免在内存中排序。
#6
-1
Early optimization is a waste of time. Just leave this problem and do the next thing. When there are 100 million rows in this table change the indexes and test what happens, until then your ten rows of data are not worth the time to "optimize".
早期的优化是浪费时间。把这个问题抛在一边，做下一件事。当这个表中有1亿个行更改索引并测试所发生的情况时，那么您的10行数据就不值得花时间去“优化”了。