sql - 大量的 Oracle I/O，即使只提取了一条记录

Question

我在我的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 访问该表。 ，但是一旦我对该表进行了一些愚蠢的投影，似乎整个表(大约 200 万行)都计划在内存中读取。我得到了很多 I/O 和缓冲区。当我从更大的查询中删除投影时，问题就消失了:

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 时，我可以在计划中重现过多的行/字节数。表。

我找到了一个 workaround for this issue .但我真的很想解释一下出了什么问题，以及如何在不过多更改查询的情况下纠正这个问题

更新

好的，经过更多的分析，我不得不说实际有问题的 I/O 是由于在其他地方使用了错误的索引。不幸的是，这在总体统计数据(或执行计划)中的预测不够充分，无法引起注意。

就这个问题而言，我仍然对执行计划中的预计 I/O 感到好奇，因为这似乎一次又一次地混淆了我们的 DBA(和我)。有时，它确实是 I/O 问题的根源......

Answer 1

有趣的是，我已经检查了各种场景，包括针对特定示例的特定解决方案。在这种情况下，将示例查询重新表述为这样可以解决问题:

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;

但是我还是不明白，如果我加入 prod，为什么 Oracle 会在其执行计划中转换如此多的 I/O在此查询的前面...