Большое количество прогнозируемых операций ввода-вывода с Oracle, даже если извлекается только одна запись

Я часто сталкиваюсь со следующей ситуацией в моих планах исполнения Oracle:

Operation                   | Object  | Order | Rows | Bytes | Projection
----------------------------+---------+-------+------+-------+-------------
TABLE ACCESS BY INDEX ROWID | PROD    |     7 |   2M |   28M | PROD.VALUE
  INDEX UNIQUE SCAN         | PROD_PK |     6 |   1  |       | PROD.ROWID

Это выдержка из более крупного плана выполнения. По сути, я получаю доступ (присоединение) к таблице с использованием первичного ключа таблицы. Как правило, существует еще одна таблица ACCO с ACCO.PROD_ID = PROD.ID, где PROD_PK - первичный ключ на PROD.ID. Очевидно, к таблице можно получить доступ с помощью UNIQUE SCAN, но как только у меня будет какая-то глупая проекция на эту таблицу, кажется, что вся таблица (около 2 миллионов строк) планируется прочитать в памяти. Я получаю много ввода-вывода и буфера. Когда я удаляю проекцию из большего запроса, проблема исчезает:

Operation                   | Object  | Order | Rows | Bytes | Projection
----------------------------+---------+-------+------+-------+-------------
TABLE ACCESS BY INDEX ROWID | PROD    |     7 |   1  |     8 | PROD.ID
  INDEX UNIQUE SCAN         | PROD_PK |     6 |   1  |       | PROD.ROWID

Я не понимаю этого поведения. Что может быть причиной этого? Заметьте, я не могу опубликовать полный запрос. Он довольно сложный и включает в себя множество расчетов. Однако шаблон часто бывает одинаковым.

ОБНОВЛЕНИЕ. Я придумал, чтобы свести мою довольно сложную настройку к простой симуляции, которая создает аналогичный план выполнения в обоих случаях (при проецировании PROD.VALUE или при его удалении):

Создайте следующую базу данных:

-- products have a value
create table prod as
select level as id, 10 as value from dual 
connect by level < 100000;
alter table prod add constraint prod_pk primary key (id);

-- some products are accounts
create table acco as
select level as id, level as prod_id from dual 
connect by level < 50000;
alter table acco 
  add constraint acco_pk primary key (id);
alter table acco 
  add constraint acco_prod_fk foreign key (prod_id) references prod (id);

-- accounts have transactions with values
create table trxs as
select level as id, mod(level, 10) + 1 as acco_id, mod(level, 17) + 1 as value
from dual connect by level < 100000;
alter table trxs 
  add constraint trxs_pk primary key (id);
alter table trxs 
  add constraint trxs_acco_fk foreign key (acco_id) references acco (id);

create index acco_i on acco(prod_id);
create index trxs_i on trxs(acco_id);

alter table acco modify prod_id not null;
alter table trxs modify acco_id not null;

Запустите следующий запрос

select v2.*
from (
  select 
    -- This calculates the balance for every transaction as a
    -- running total, subtracting trxs.value from the product value
    --
    -- This is the "projection" I mentioned that causes I/O. Leaving it
    -- away (setting it to 0), would improve the execution plan
    prod.value - v1.total balance,
    acco.id acco_id
  from (
    select 
      acco_id,
      sum(value) over (partition by acco_id
                       order by id
                       rows between unbounded preceding 
                       and current row) total
    from trxs
  ) v1
  join acco on v1.acco_id = acco.id
  join prod on acco.prod_id = prod.id
) v2
-- This is the single-row access predicate. From here, it is
-- clear that there can only be 1 acco and 1 prod
where v2.acco_id = 1;

Проанализировать

При анализе планов выполнения вышеуказанного запроса (с проекцией PROD.VALUE или без нее) я могу воспроизвести чрезмерное количество строк/байтов в плане при обращении к таблице prod.

Я нашел обходной путь для этой проблемы. Но мне действительно интересно объяснить, что происходит не так, и как я могу исправить эту проблему, не меняя слишком много запросов.

Update

ОК, после гораздо большего анализа, я должен сказать, что фактический проблемный ввод-вывод был вызван неправильным индексом, используемым где-то совсем другим. К сожалению, это было недостаточно хорошо прогнозировать в общей статистике (или в плане выполнения), чтобы заметить.

Что касается этого вопроса, я все равно интересуюсь прогнозируемым вводом-выводом в плане выполнения, поскольку это, похоже, снова и снова путает наших администраторов баз данных (и меня). И иногда, это действительно является источником проблем ввода-вывода...

Ответы

select
  -- Explicitly project value in a nested loop. This seems to be much cheaper
  -- in this specific case
  (select value from prod where id = v2.prod_id) - v2.balance,
  v2.acco_id
from (
  select 
    -- Now, balance is only a running total, not the running total
    -- added to PROD.VALUE
    v1.total balance,
    acco.id acco_id,
    acco.prod_id prod_id
  from (
    select 
      acco_id,
      sum(value) over (partition by acco_id
                       order by id
                       rows between unbounded preceding 
                       and current row) total
    from trxs
  ) v1
  -- The JOIN of PROD is no longer needed
  join acco on v1.acco_id = acco.id
) v2
where v2.acco_id = 1;