Блог

Насколько я понимаю, предполагаемое количество строк в плане объяснения для источника внутренней строки соединения вложенного цикла отражает количество строк только для одной итерации этого вложенного цикла.

В следующем примере шаг 6 плана объяснения представляет собой источник внутренней строки соединения вложенного цикла, который получает строку по одному идентификатору строки за раз. Следовательно, в нем должно быть оценено количество строк, равное 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 источника внутренней строки <> Шаг 1 вложенного цикла.

Я согласен, что общее количество шагов 1 должно быть 200, но не понимаю, почему на шаге 6 количество строк оценивается в 100.

Почему на шаге 6 оценочные строки равны 100 вместо 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 раз каждый раз, чтобы получить одну строку.

Я думаю, что этот параграф документации Oracle довольно хорошо объясняет этот сценарий:

Несколько операций вложенных циклов могут иногда отображаться в плане выполнения только для одного соединения, что указывает на то, что Oracle использовала метод оптимизации пакетной обработки вложенных циклов. Что делает этот метод, так это преобразует одно соединение двух источников строк в соединение источника исходной строки с одной копией источника пробной строки, который соединен с репликой самого себя в ROWID; поскольку теперь у нас есть три источника строк, нам нужно как минимум два вложенных цикла. Копия источника пробной строки, которая используется для выполнения самостоятельного соединения с ROWID, используется для фильтрации строк, поэтому она будет иметь соответствующую TABLE ACCESS BY ... ROWID запись в плане выполнения. Эта оптимизация, основанная на затратах, часто может сократить ввод-вывод, хотя план выполнения может не отображать преимущества прозрачно.

Шаг 6 в вашем примере — это «копия источника строки проверки»; это в основном кэшированная версия таблицы C, поэтому в ней 100 строк. Но его стоимость распределяется между всеми внешними вложенными циклами — к таблице был получен доступ только один раз, поэтому она уже включена в общую сумму с шага 2. (Я думаю?)