原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2017/05/08/opt_estimate/
译文如下：
opt_estimate HINT 是许多不应该在最终用户代码中使用的提示之一，并且没有正式的文档记录。然而，就像其他许多提示一样，当您看到它在Oracle软件生成的代码周围浮动时，很难忽略它。这条信息是由Oak Table成员Stefan Koehler在twitter上提出的一个问题引起的，该问题询问提示的index_filter参数是否有效。在查看我的库时，我知道答案是肯定的，所以在twitter上快速交流之后，我说我要写一个关于我的例子的简短说明，就这样。
尽管这不是应该使用的提示，但还是值得写这篇注释，提醒您对Oracle在执行计划的谓词部分报告的access谓词和filter谓词进行索引范围扫描的重要性。
当查询进行索引范围扫描时，它将遍历一组（逻辑上）连续的索引叶子块，依次查看每个单独的索引项（这些索引项将在叶块中正确地“排序”），以确定它是否应该使用它在那里找到的rowid来访问表。为了“完美”地使用索引，Oracle可能能够确定它在索引中所需访问的起始位置和结束位置，并知道它应该使用其间的每个rowid来访问表，这样就不会在途中“浪费”检查索引项。但是，在涉及多列索引和多个谓词的查询中，Oracle可能必须在索引的第一列上使用谓词来标识起始位置和结束位置，但是在索引中后面的列上使用更多谓词来决定是否使用每个索引项访问表。
Oracle可以用来标识它应该访问的叶块的范围的谓词称为access谓词，而Oracle可以用来进一步消除rowid的谓词称为filter谓词。
演示这一点的最简单方法是使用以下形式的查询：“Index_Column1=…and Index_Column3=…”，这就是我将在模型中使用的：
rem
rem Script: opt_est_ind_filter.sql
rem Author: Jonathan Lewis
rem Dated: March 2017
rem
rem Last tested
rem 18.3.0.0
rem 12.2.0.1
rem 11.2.0.4
rem 10.2.0.5
rem
create table t1
nologging
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 --> comment to avoid WordPress format issue
)
select
rownum id,
mod(rownum - 1,100) n1,
rownum n2,
mod(rownum - 1, 100) n3,
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(n1,n2,n3) nologging;
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T1',
method_opt => 'for all columns size 1'
);
end;
/
select leaf_blocks from user_indexes where index_name = 'T1_I1';
索引的叶子块是3062个：
我定义了n1和n3来匹配，对于0到99之间的每一个值，表中有10000行，其中n1和n3等于该值。但是，在（n1，n3）上没有定义组合列的情况下，优化器将使用其标准的“无相关性”算法来确定n1和n3有10000个可能的组合，每个组合有100行。让我们看看这对如下简单查询有什么作用：
set autotrace traceonly explain
select count(v1)
from t1
where n1 = 0 and n3 = 0
;
set autotrace off;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 134 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 134 (1)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 100 | | 34 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"=0 AND "N3"=0)
filter("N3"=0)
执行计划显示了一个索引范围扫描，其中n3=0用作filter谓词，n1=0（并且从n3=0部分获得了一点额外的精度）用作access谓词。优化器计算出将从索引中检索出100个rowid，并用于在表中查找100个行。
范围扫描的成本是34：优化器的估计是，初始访问索引的规模将由谓词n1=0引起，该谓词占索引的1%——给我们3062/100个叶块（四舍五入）。此外，从索引的blevel向下访问的过程会产生一些额外的开销，而CPU使用会产生一些额外的开销。
现在让我们告诉优化器，它的基数估计值是25倍（而不是我们实际上知道的100倍）的两种不同方式之一：
prompt ============================
prompt index_scan - scale_rows = 25
prompt ============================
select
/*+
qb_name(main)
index(@main t1(n1, n2, n3))
opt_estimate(@main index_scan t1, t1_i1, scale_rows=25)
*/
count(v1)
from t1
where n1 = 0 and n3 = 0
;
prompt ==============================
prompt index_filter - scale_rows = 25
prompt ==============================
select
/*+
qb_name(main)
index(@main t1(n1, n2, n3))
opt_estimate(@main index_filter t1, t1_i1, scale_rows=25)
*/
count(v1)
from t1
where n1 = 0 and n3 = 0
;
在这两个例子中，我都提示索引可以阻止优化器切换到表扫描；但是在第一种情况下，我告诉Oracle，整个索引范围扫描必须放大25倍，而在第二种情况下，我告诉Oracle，由于最终的filter，它的估计值必须放大25倍。这对计划的成本和基数有何影响。
============================
index_scan - scale_rows = 25
============================
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3285 (1)| 00:00:17 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 3285 (1)| 00:00:17 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 2500 | | 782 (2)| 00:00:04 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"=0 AND "N3"=0)
filter("N3"=0)
==============================
index_filter - scale_rows = 25
==============================
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2537 (1)| 00:00:13 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 100 | 1700 | 2537 (1)| 00:00:13 |
|* 3 | INDEX RANGE SCAN | T1_I1 | 2500 | | 34 (3)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("N1"=0 AND "N3"=0)
filter("N3"=0)
在这两种情况下，索引范围扫描的基数估计值都增加了25倍。不过，请注意，优化器现在正遭受认知失调的困扰——它“知道”通过访问表它将得到2500个行，它“知道”在到达表中时没有额外的谓词来消除表中的行，但它也“知道”它只会找到100行。使用opt_estimate（）和cardinality（）提示很难搞乱，即使是在一般的小情况下也是如此——就像所有的HINT一样，要达到想要的结果，需要一到两个以上的提示。
就本说明而言，更重要的是成本。当我们使用index_filter参数时，优化器仍然认为它将访问相同数量的叶块，它唯一要做的更正就是在这些块中找到的rowid的数量-因此索引范围扫描成本没有改变（尽管我认为在某些情况下，由于CPU成本的增加，它可能会略有变化）。当我们使用index_scan参数时，优化器会放大它对叶块数量的估计（因此是成本），如图782/25=31.28所示。（没有进入跟踪文件并检查与之前报告的34个足够接近的确切细节，我认为它允许25倍的叶块数量加上更多的CPU）。
结论
正如我在一开始所说的，opt_estimate（）确实不是一个您应该使用的提示，但是我希望这篇文章有助于阐明access谓词和filter谓词相对于索引范围扫描的成本的重要性。
脚注
我在笔记中有两个重要的细节。首先是表达“看起来好像”的频率——这是我“真的应该在发表任何结论之前再做一些测试”的简写；其次，我最近的测试是在10.2.0.5上进行的（由于统计信息中的抽样，结果略有不同）。考虑到Stefan Koehler在他的版本中提到了11.2.0.3，我运行了11.1.0.7的一个实例，发现index_filter示例没有扩大基数，所以他的问题可能是版本问题。
Update June 2019
我只是有理由重新阅读这篇文章，所以我针对12.2.0.1、18.3.0.0和19.3.0.0运行了我的测试用例，提示仍然如上面所述（尽管由于CPU成本的微小变化，成本有一些小的变化）。我还被提示（晚了两年）完成了一篇关于opt_estimate（）在嵌套循环连接中的行为的注释，这是我在发布这篇文章几个月后开始的。
