Большой объем прогнозируемых операций ввода-вывода с 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's 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.

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

Обновление

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

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