原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2015/07/17/descending-indexes/
译文如下：
到目前为止，我已经写过有关降序索引的优化器BUG的问题，但是几天前OTN数据库论坛上出现了一个问题，这使我决定仔细研究一下这个算法显然有缺陷的例子。这个表具有两个索引，一个在日期列（TH_UPDATE_TIMESTAMP）上，另一个在复合索引中以该列降序开头（TH_UPDATE_TIMESTAMP DESC，TH_TXN_CODE）。优化器选择了明显是错误的降序索引（即使刷新了统计信息并且已消除了所有与基于日期的索引有关的常见错误之后）。下面是输出的执行计划，该执行计划表明优化器存在的问题：
SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| TXN_HEADER_IDX17 | 1083K| 8462K| 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
这里有两个线索：首先，（几乎可以确定）单列索引是更好的选择时，Oracle使用了（较大的）两列索引，仅仅是因为它在查询过程中通过降序访问可能会更小。其次，通过（隐式b树）索引范围扫描来获取100万个rowid的成本为4，这将需要每个叶块至少250,000个索引条目以保持较低的成本（或者是2,500，如果将optimizer_index_cost_adj设置为1，因此只有32KB的块大小才有可能）。
作者尽可能提供了所需的信息，并反馈我们提出的任何建议；在此过程中，我们得到的信息是表具有约9000万行，并且此“timestamp”列具有约4500万个不同的值，范围从2012年9月6日到2015年7月2日，不包含空值。
基于此信息，我在11.2.0.4的实例中对问题进行了建模（作者使用的是11.2.0.3）。
rem
rem Script: descending_bug_05.sql
rem Author: Jonathan Lewis
rem Dated: Apr 2015
rem
create table t1
nologging
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid wordpress format issue
)
select
rownum id,
to_date('06-SEP-2012 15:13:00','dd-mon-yyyy hh24:mi:ss') +
trunc((rownum - 1 )/4) / (24 * 60) upd_timestamp,
mod(rownum - 1,10) txn_code,
rpad('x',50) padding
from
generator v1,
generator v2
where
rownum <= 4 * 1030 * 24 * 60 -- > comment to avoid wordpress format issue
;
create index t1_asc on t1(upd_timestamp) nologging;
create index t1_desc on t1(upd_timestamp desc) nologging;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T1',
method_opt => 'for all columns size 1',
cascade => false
);
end;
/
从2012年9月6日到2015年7月3日，我的数据集每分钟有4行，在upd_timestamp列上同时具有上升和下降索引。作为参考，以下是有关两个索引的统计信息：
INDEX_NAME SAMPLE BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
-------------------- ---------- ---------- ----------- -----------------
T1_DESC 5932800 2 17379 154559
T1_ASC 5932800 2 15737 59825
升序索引较小，并且clustering_factor明显更小，因此对于其中任一索引的使用都是可行的选项，升序索引是优化器应该选择的索引（我们认为）。顺便说一句，5.9M索引条目恰好是表中的行数–这些统计信息是在创建索引时自动计算的。
这是一个简单的查询，其中包含从内存中提取并执行的执行计划（包含实际执行步骤的信息）。
select max(id) from t1 where upd_timestamp between
to_date('01-jun-2015','dd-mon-yyyy') and
to_date('30-jun-2015','dd-mon-yyyy')
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 (100)| 1 |00:00:01.29 | 4710 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:01.29 | 4710 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 167K| 29 (0)| 167K|00:00:00.98 | 4710 |
|* 3 | INDEX RANGE SCAN | T1_DESC | 1 | 885 | 5 (0)| 167K|00:00:00.35 | 492 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."SYS_NC00005$" .ge. HEXTORAW('878CF9E1FEF8FEFAFF') AND
"T1"."SYS_NC00005$" .le. HEXTORAW('878CF9FEF8FEF8FF') )
filter((SYS_OP_UNDESCEND("T1"."SYS_NC00005$") .ge. TO_DATE(' 2015-06-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND SYS_OP_UNDESCEND("T1"."SYS_NC00005$") .le. TO_DATE(' 2015-06-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
尽管我对过滤条件都采用了实际的值分布，并且查询非常简单，优化器应该能够获得良好的估算，但是优化器对885个ROWID的索引估算与实际的行数167,000相差很远。提醒一下，谓词部分比预期的要混乱得多，并且表估算行数是正确的（尽管当前与索引估算不一致）。
下面是使降序索引不可见时得到的执行计划：
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2146 (100)| 1 |00:00:01.25 | 2134 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:01.25 | 2134 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 167K| 2146 (1)| 167K|00:00:00.94 | 2134 |
|* 3 | INDEX RANGE SCAN | T1_ASC | 1 | 167K| 453 (2)| 167K|00:00:00.31 | 446 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("UPD_TIMESTAMP" .ge. TO_DATE(' 2015-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"UPD_TIMESTAMP" .le. TO_DATE(' 2015-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
在降序索引不可见的情况下，我们可以看到升序索引的行数估算正确，并注意到这会使成本增加了多少。优化器选择了错误的索引而导致成本的差异也就不足为奇了。现在的问题是，为什么优化器估算索引的cardinality（即选择性，进而算作成本）产生了如此严重的偏差。
对于降序索引，答案是优化器通过将日期存储为字符串而犯了与应用程序相同的错误。它对基于虚拟列记录的sys_op_descend（）值使用了基于范围的常规计算方式，并且对这些值代表日期这一事实一无所知。我可以通过再创建一个表，插入几行，收集统计数据并向您展示几个“降序”日期的内部存储情况来最容易地演示这一点。
drop table t2;
create table t2 (d1 date);
create index t2_i1 on t2(d1 desc);
insert into t2 values('01-Jun-2015');
insert into t2 values('30-Jun-2015');
commit;
select d1, sys_nc00002$ from t2;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T2',
method_opt => 'for all columns size 1',
cascade => false
);
end;
/
column endpoint_value format 999,999,999,999,999,999,999,999,999,999,999,999
break on table_name skip 1
select
table_name, column_name, endpoint_number, endpoint_value, to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') end_hex
from
user_tab_histograms
where
table_name in ('T1','T2')
and column_name like 'SYS%'
order by
table_name, column_name, endpoint_number
;
我将日期2015年6月1日和2015年6月30日放入表中，因为这些是我在查询的where子句中使用的值。以下是显示降序索引列的内部表示以及在t1和t2中存储的虚拟列的最小值和最大值的结果。
D1 SYS_NC00002$
--------- ------------------------
01-JUN-15 878CF9FEF8FEF8FF
30-JUN-15 878CF9E1FEF8FEFAFF
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE END_HEX
-------------------- -------------------- --------------- ------------------------------------------------ ---------------------------------
T1 SYS_NC00005$ 0 703,819,340,111,320,000,000,000,000,000,000,000 878CF8FCEFF30BCEBC8823F7000000
SYS_NC00005$ 1 703,880,027,955,346,000,000,000,000,000,000,000 878FF6F9EFF20256F3B404F7400000
T2 SYS_NC00002$ 0 703,819,411,001,549,000,000,000,000,000,000,000 878CF9E1FEF8F24C1C7CED46200000
SYS_NC00002$ 1 703,819,419,969,389,000,000,000,000,000,000,000 878CF9FEF8FEEED28AC5B22A200000
如果对照直方图数据中的end_hex值检查t2表数据（sys_nc00002 $）值，会看到它们与前6个字节（12位数字）匹配。Oracle已经完成了其标准处理 -取列的前6个字节，从隐式转换为十进制，四舍五入到最高15位数字（技术上四舍五入（值，-21）），并将结果转换为十六进制并存储。
因此，让我们做一些算术运算。边界内的范围扫描的选择性（非正式地）为：“range we want” / “total range”。我已经设置了t2以向我们显示计算所需范围所需的值，并且我已经报告了t1值以允许我们计算总范围，我们只需要减去较低的值 (endpoint number 0)从两个sys_nc0000N $列的较高值开始。因此（忽略所有21个零），我们的选择性是：
• （703,819,419,969,389 – 703,819,411,001,549）/（703,880,027,955,346 – 703,819,340,111,320）= 0.00014777
• 我们表中有590万行，因此cardinality 估计应约为：5,932,800 * 0.00014777 = 876.69
实际cardinality 估算为885，但我们不允许使用基于范围的谓词的确切形式：我们应在两个边界添加1 / num_distinct，因为范围是封闭查询（大于或等于，小于或等于等于）–将基数估算值提高到884.69，四舍五入到优化器产生的885。
结论
该说明表示，在处理与索引中降序列关联的虚拟列时，Oracle（至少在日期方面）正在丢失有关统计信息的数据。正如所证明的那样，这可能导致ORACLE认为基于原始列的谓词的选择性估计极差，并且这些选择性估算使Oracle选择了错误的索引，并在整个执行计划的计算中使用了虚假的低成本。
脚注
这篇笔记似乎与之前笔记中的问题 11072246 “具有DESC索引的列的cardinality估算错误”相匹配，但是该错误在12.1中报告为已修复，而此测试用例在12.1.0.2中得以重现。最近的测试表明，此问题已在12.2.0.1中已修复。
