原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/01/31/descending-problem/
我以前写过关于降序索引的奇怪之处（例如在ORACLE降序索引BUG，ORACLE降序索引问题和FBI错误重现）。但我刚刚遇到一个例子，我可能必须引入一个不需要存在的降序索引。通常，这是两个Oracle功能产生碰撞的边界。我有一个记录大量客户数据的表，这个表按年记录了一定数量的交易信息。我需要一个查询显示客户的最新交易数据。很自然的，这个表被设计成了按客户ID的HASH分区表。我有一个以customer_id和transaction_date开头的索引列。因此，下面是我的查询，或更准确地说，是客户经过简化并调整后的查询场景：
select /*+ gather_plan_statistics */
*
from (
select
v1.*,
rownum rn
from (
select /*
no_eliminate_oby
index_rs_desc(t1 (customer_id, transaction_date))
*/
t1.*
from t1
where customer_id = 50
and transaction_date >= to_date('1900-01-01','yyyy-mm-dd')
order by transaction_date DESC
) v1
where rownum <= 10 -- > comment to avoid WordPress format issue
)
where rn >= 1
;
可以看到SQL中加入了一些HINT，/*+ gather_plan_statistics */允许我在执行时同时收集对应步骤的实际行的资源信息。并且内联视图中的HINT（我已经注释掉），将强制数据库使用customer_id和transaction_date上的索引进行降序扫描。
如果我将t1表创建为一个简单的（非分区）堆表，将得到以下执行计划（我不得不编辑一个“小于或等于”的符号来避免WordPress格式问题）
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 (100)| 10 |00:00:00.01 | 14 |
|* 1 | VIEW | | 1 | 10 | 14 (0)| 10 |00:00:00.01 | 14 |
|* 2 | COUNT STOPKEY | | 1 | | | 10 |00:00:00.01 | 14 |
| 3 | VIEW | | 1 | 10 | 14 (0)| 10 |00:00:00.01 | 14 |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 340 | 14 (0)| 10 |00:00:00.01 | 14 |
|* 5 | INDEX RANGE SCAN DESCENDING| T1_I1 | 1 | 10 | 3 (0)| 10 |00:00:00.01 | 4 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM .LE. 10)
5 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
注意到索引降序扫描正是我所需要的，它只消耗了最小的逻辑读开销。并且从表中只访问了最必要的10行。如果我把T1表创建为一个HASH分区表，且相应索引创建为本地索引后。看一下将发生什么，下面是不加HINT的执行计划：
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 207 (100)| 10 |00:00:00.01 | 138 | | | |
|* 1 | VIEW | | 1 | 10 | 207 (1)| 10 |00:00:00.01 | 138 | | | |
|* 2 | COUNT STOPKEY | | 1 | | | 10 |00:00:00.01 | 138 | | | |
| 3 | VIEW | | 1 | 340 | 207 (1)| 10 |00:00:00.01 | 138 | | | |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 340 | 207 (1)| 10 |00:00:00.01 | 138 | 2048 | 2048 | 2048 (0)|
| 5 | PARTITION HASH SINGLE | | 1 | 340 | 206 (0)| 340 |00:00:00.01 | 138 | | | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 1 | 340 | 206 (0)| 340 |00:00:00.01 | 138 | | | |
|* 7 | INDEX RANGE SCAN | T1_I1 | 1 | 340 | 4 (0)| 340 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM. LE. 10)
4 - filter(ROWNUM .LE. 10)
7 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE">=TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRANSACTION_DATE" IS NOT NULL)
尽管优化器使用本地索引仅做了单分区扫描，但是他并没有采用我们希望的索引降序扫描。尽管他已经采用了合适的索引。因此他用错误的访问顺序获取了满足条件的相关行，然后不得不对他们进行排序，并只保留最大的10行数据。我们看到发生了138个逻辑读（这些都会转换为实际的IO访问，甚至更多的资源开销）。
这是否意味着对表进行分区后优化器无法使用索引降序扫描？或者估算成本过高导致上述问题？下面是带有HINT的执行计划，我们看下优化器走降序扫描时的场景：
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 207 (100)| 10 |00:00:00.01 | 8 |
|* 1 | VIEW | | 1 | 10 | 207 (1)| 10 |00:00:00.01 | 8 |
|* 2 | COUNT STOPKEY | | 1 | | | 10 |00:00:00.01 | 8 |
| 3 | VIEW | | 1 | 340 | 207 (1)| 10 |00:00:00.01 | 8 |
| 4 | PARTITION HASH SINGLE | | 1 | 340 | 206 (0)| 10 |00:00:00.01 | 8 |
| 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T1 | 1 | 340 | 206 (0)| 10 |00:00:00.01 | 8 |
|* 6 | INDEX RANGE SCAN DESCENDING | T1_I1 | 1 | 340 | 4 (0)| 16 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM .LE. 10)
6 - access("CUSTOMER_ID"=50 AND "TRANSACTION_DATE" IS NOT NULL AND "TRANSACTION_DATE">=TO_DATE('
1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
可以看到使用降序扫描后只消耗了8个逻辑读，从表中只访问了10行。但是看到成本值，优化器并没有认为使用降序扫描能有任何的开销降低。回到普通（非分区）表的执行计划，可以看到优化器确实使用了访问更少记录的降序扫描。所以这里的问题是评估成本的问题-我们不得不使用HINT让优化器走索引降序扫描，如果我们希望减少Oracle所做的工作。
你可能会注意到，在索引降序扫描步骤中返回的行数是16而不是10，在普通表查询时没有该问题。我不知道为什么会这样，但是当我将需求更改为20行时，范围扫描返回了31行，当我将其更改为34行时，范围扫描返回了46行，而查询47行是实际返回了61行。可以看到该趋势，索引范围扫描返回的行数似乎为1 + 15 * N。
脚注：
如果将避免添加HINT（或SQL patch）。只需要重新创建一个按transaction_date列降序存储的索引即可。此时优化器将自动选择正确的路径，然后查询恰好返回10行，并且不需要进行任何排序。但是谁想要在不需要的时候创建降序索引呢！
如果您想重现实验，请使用以下脚本创建我的测试数据。
rem
rem Script: pt_ind_desc_bug.sql
rem Author: Jonathan Lewis
rem Dated: Dec 2018
rem Purpose:
rem
rem Last tested
rem 18.3.0.0
rem 12.2.0.1
rem 12.1.0.2
rem
create table t1 (
customer_id,
transaction_date,
small_vc,
padding
)
partition by hash(customer_id) partitions 4
nologging
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid WordPress format issue
)
select
mod(rownum,128) customer_id,
(trunc(sysdate) - 1e6) + rownum transaction_date,
lpad(rownum,10,'0') v1,
lpad('x',100,'x') padding
from
generator v1,
generator v2
where
rownum <= 1e6 -- > comment to avoid WordPress format issue
;
create index t1_i1 on t1(customer_id, transaction_date)
local
nologging
;
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1',
method_opt => 'for all columns size 1'
);
end;
/
我已经在12.1.0.2、12.2.0.1和18.3.0.0上运行了该测试-在所有三个版本中，行为都是相同的。
正如客户在阅读该帖子后提醒我的那样，值得指出的是，对于更复杂的SQL，您仍然必须考虑基数和成本计算中的错误，这些错误很容易将优化器推入错误的连接顺序和/或连接方法。因此您是选择提示升序索引还是创建降序索引。评估此类“分页”查询所需的执行计划和优化方式可能会更复杂。
