Oracle:嵌套循环的内部行源-估计的行不正确

Question

我的理解是，嵌套循环联接的内部行源的解释计划中估计的行数反映了该嵌套循环仅一次迭代的行数。

在下面的示例中，解释计划的步骤6是嵌套循环联接的内部行源，该联接一次获取一个ROWID的行。因此，它应该估计有1行(每个ROWID只有1行(。

为什么步骤6的table access by index ROWID显示100(我希望它显示1(？

使用Oracle 19c Enterprise Edition。

drop table "C";
drop table "P";
create table "P"
( p_id  NUMBER
, p_num NUMBER
, p_pad CHAR(200 byte)
)
;
insert
into "P" (p_id, p_num, p_pad)
select level
, mod(level-1,200/2)
, ' '
from dual
connect by level <= 200
;
create table "C"
( c_id  NUMBER
, p_id  NUMBER
, c_pad CHAR(200 byte)
)
;
insert /*+ append enable_parallel_dml parallel (auto) */
into "C" (c_id, p_id, c_pad)
with
"D" as
( select /*+ materialize */ null from dual connect by level <= 100
)
select rownum      c_id
, p_id        p_id
, ' '         c_pad
from "P", "D"
;
commit;

create index IX_P on p (p_num);   
create unique index IU_P on p (p_id);
alter table p add constraint UK_P unique (p_id) rely using index IU_P enable validate;
alter table C add constraint R_C foreign key (p_id) references p (p_id) rely enable validate;
create index IR_C on _C (p_id);
exec dbms_stats.gather_table_stats(OwnName => null, TabName => 'P', cascade => true);
exec dbms_stats.gather_table_stats(OwnName => null, TabName => 'C', cascade => true);

select /*+ optimizer_features_enable('19.1.0')
use_nl (P C) */
*
from "P"
join "C"
on P.p_id = C.p_id
and P.p_num = 1
;
plan hash value: 3840235794
----------------------------------------------------------------------------------------------
| id  | Operation                             | name | rows  | Bytes | cost (%CPU)| time     |
----------------------------------------------------------------------------------------------
|   0 | select statement                      |      |   200 | 83000 |   205   (0)| 00:00:01 |
|   1 |  nested LOOPS                         |      |   200 | 83000 |   205   (0)| 00:00:01 |
|   2 |   nested LOOPS                        |      |   200 | 83000 |   205   (0)| 00:00:01 |
|   3 |    table access by index ROWID BATCHED| P    |     2 |   414 |     3   (0)| 00:00:01 |
|*  4 |     index range scan                  | IX_P |     2 |       |     1   (0)| 00:00:01 |
|*  5 |    index range scan                   | IR_C |   100 |       |     1   (0)| 00:00:01 |
|   6 |   table access by index ROWID         | C    |   100 | 20800 |   101   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("P"."P_NUM"=1)
5 - access("P"."P_ID"="C"."P_ID")

外部行源步骤3乘以内部行源步骤5=嵌套循环步骤2。

然而，外部行源步骤2乘以内部行源步骤6<gt；嵌套循环步骤1。

我同意步骤1的总数应该是200，但不明白为什么步骤6估计有100行。

为什么步骤6的估计行数为100而不是1？

提前谢谢。

Answer 1

在这里，您可以看到外部NESTED LOOP中预期的行数

select p_id, count(*) from C where p_id in (
select p_id from P where p_num = 1)
group by p_id;
P_ID   COUNT(*)
---------- ----------
2        100
102        100

因此，实际上每个迭代都期望得到100行。

如果使用提示gather_plan_statistics运行查询，则可以看到Starts的数量和实际的总行数A-Rows。

select /*+ gather_plan_statistics use_nl (P C) */
*
from "P"
join "C"
on P.p_id = C.p_id
and P.p_num = 1
SQL_ID  927pggk6scpwt, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl (P C) */        *   from "P"   
join "C"          on P.p_id = C.p_id             and P.p_num = 1

Plan hash value: 2326820011

--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |      1 |        |    200 |00:00:00.01 |     213 |
|   1 |  NESTED LOOPS                         |      |      1 |    200 |    200 |00:00:00.01 |     213 |
|   2 |   NESTED LOOPS                        |      |      1 |    200 |    200 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| P    |      1 |      2 |      2 |00:00:00.01 |       5 |
|*  4 |     INDEX RANGE SCAN                  | IX_P |      1 |      2 |      2 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN                   | IR_C |      2 |    100 |    200 |00:00:00.01 |       8 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | C    |    200 |    100 |    200 |00:00:00.01 |     200 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("P"."P_NUM"=1)
5 - access("P"."P_ID"="C"."P_ID")

即，操作5被启动两次(列Starts(，总计为200行(列A-Rows(

操作6每次启动200次，以获得一行。

Answer 2

我认为Oracle文档的这一段很好地解释了这种情况：

只有一个联接的执行计划中偶尔会出现多个嵌套循环操作，这表明Oracle使用了嵌套循环批处理优化技术。该方法的作用是将两个行源的单个连接转换为驱动行源与探测行源的一个副本的连接，探测行源与ROWID上自身的副本连接；由于我们现在有三个行源，我们至少需要两个嵌套循环。用于对ROWID执行自联接的探测行源副本用于筛选行，因此在执行计划中会有相应的TABLE ACCESS BY ... ROWID条目。这种基于成本的优化通常可以减少I/O，尽管执行计划可能不会透明地显示好处。

示例中的步骤6是"；探测行源拷贝"；；它基本上是表C的缓存版本，所以它有100行。但它的成本在所有外部嵌套循环中共享——表只访问过一次——所以它已经包含在步骤2的总数中了。(我想？(