我的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问题的根源...
最佳答案
值得注意的是，我已经检查了各种场景，包括针对特定示例的特定解决方案。重新改写示例查询，这样可以解决这种情况下的问题:
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 ...关于sql - 使用Oracle进行大量预计的I/O，即使仅获取一条记录也是如此，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9671477/