原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2019/06/28/opt_estimate-3/
译文如下：
对于opt_estimate()提示，这只是一个简短的提示：它们可能是最有用的变体，因为它们解决了优化器可能产生持续错误的CARDINALITY估算的问题。第一个是“group by”选项——这个提示我曾经称之为“战略性”提示，但更确切地说，应该称之为“查询块”提示。以下是最简单的示例（在12.2、18.3和19.2下测试）：
rem
rem Script: opt_est_gby.sql
rem Author: Jonathan Lewis
rem Dated: June 2019
rem
create table t1
as
select
rownum id,
mod(rownum,200) n1,
lpad(rownum,10,'0') v1,
rpad('x',100) padding
)
from
dual
connect by
level <= 3000
;
set autotrace on explain
prompt =============================
prompt Baseline cardinality estimate
prompt (correct cardinality is 10)
prompt Estimate will be 200
prompt =============================
select /*+
qb_name(main)
*/
mod(n1,10), count(*)
from t2
group by
mod(n1,10)
;
我已经生成了一个包含3000行的表，其中列n1包含15类值，每类包含200个不同的值。然后，查询在mod（n1,10）上聚合操作，因此它只会返回10行，但是优化器没有一个机制来推断这一点，在第一步的HASH GROUP BY操作上的Rows值是我们真正感兴趣的。
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 800 | 10 (10)| 00:00:01 |
| 1 | HASH GROUP BY | | 200 | 800 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------
似乎优化器的默认位置是使用底层列的num_distinct作为聚合的估计值。我们可以用opt_estimate（）提示，用通常的两种方法来解决这个问题。首先，让我们告诉优化器它高估了基数10倍：
select /*+
qb_name(main)
opt_estimate(@main group_by, scale_rows = 0.1)
*/
mod(n1,10), count(*)
from t1
group by
mod(n1,10)
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 80 | 10 (10)| 00:00:01 |
| 1 | HASH GROUP BY | | 20 | 80 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------
HINT使用group_by作为关键选项参数，然后使用标准的scale_rows = nnn设置比例因子，该比例因子应用于调整默认计算的结果。设为10％（0.1）时，我们估算有20行。
或者，我们可以简单地告诉优化器，我们希望它相信将为聚合生成多少行——让我们告诉它，结果将是10行。
select /*+
qb_name(main)
opt_estimate(@main group_by, rows = 10)
*/
mod(n1,10), count(*)
from t1
group by
mod(n1,10)
;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 10 (10)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 40 | 10 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------------
我们将相同的group_by用作关键参数，并且通过rows = nnn。
下一步：
在聚合之后，通常会有一个“having”子句，因此如果您知道having子句的正常效果，那么可以考虑使用group_by选项来修复having子句的基数。例如：“having count（）>NNN”将使用优化器的标准5%“可选择率”和“having count（）=NNN”将使用标准的1%“可选择率”。但是，在看过group_by options之后，我猜测opt_estimate()提示可能也有一个having选项，所以我尝试了一下–启用了autotrace之后，这里有三个查询，首先是没有HINT的基线（在having子句中使用标准的5%“可选择率”），然后是几个其他查询，其中有一些提示可以调整基数：
select /*+
qb_name(main)
*/
mod(n1,10), count(*)
from t1
group by
mod(n1,10)
having
count(*) > 100
;
select /*+
qb_name(main)
opt_estimate(@main having scale_rows=0.4)
*/
mod(n1,10), count(*)
from t1
group by
mod(n1,10)
having
count(*) > 100
;
select /*+
qb_name(main)
opt_estimate(@main group_by scale_rows=2)
opt_estimate(@main having scale_rows=0.3)
*/
mod(n1,10), count(*)
from t1
group by
mod(n1,10)
having
count(*) > 100
;
第一个查询给我们的基线基数是10（200的5%）。第二个查询将having基数缩小0.4倍（表示估计值为4）。最后一个查询将group by cardinality加倍（到400），然后将having cardinality（本应为20）缩小0.3倍，产生1个cardinality为 6的效果。下面是执行计划：
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 10 (10)| 00:00:01 |
|* 1 | FILTER | | | | | | -- 10
| 2 | HASH GROUP BY | | 10 | 40 | 10 (10)| 00:00:01 | -- 200
| 3 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 16 | 10 (10)| 00:00:01 |
|* 1 | FILTER | | | | | | -- 4
| 2 | HASH GROUP BY | | 4 | 16 | 10 (10)| 00:00:01 | -- 200
| 3 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 24 | 10 (10)| 00:00:01 |
|* 1 | FILTER | | | | | | -- 6
| 2 | HASH GROUP BY | | 6 | 24 | 10 (10)| 00:00:01 | -- 400
| 3 | TABLE ACCESS FULL| T1 | 3000 | 12000 | 9 (0)| 00:00:01 |
----------------------------------------------------------------------------
有点遗憾的是，FILTER操作不显示估算值，而HASH GROUP BY操作显示的是应用了having子句之后的估计值。很高兴看到执行计划报告了我在步骤1和2的末尾添加的提示。
你可能会想知道，为什么会想增加对group by的估计，然后减少对having的估算。虽然我不打算费心去创建一个有效的示例，但是应该不难理解这样一个想法：优化器可能会使用复杂视图合并将group by推迟到联接之后，所以增加group by的估计值可能是必要的，以确保接下来的特定转换不会发生，通过对having的缩减，可以确保下一个联接是嵌套循环而不是HASH联接。当然，如果您不需要如此精细，您可以简单地利用opt_estimate()提示的另一个选项query_block选项–但这（可能）将出现在本系列的下一篇文章中。
