You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When you have a join and a WHERE clause containing an EXISTS predicate that refers to the first joined stream, that EXISTS predicate could be evaluated early (right after the first stream is fetched), but in fact it's evaluated later (after the second stream is fetched) thus not being able to filter the unnecessary rows out from the join in advance and reduce the execution time. This is a regression introduced in v1.5.
Test case:
create table TEST(ID integer not null);
alter table test add constraint PK_TEST primary key (ID);
create generator GEN_TEST;
commit work;
insert into TEST(id) values (gen_id(GEN_TEST, 1));
insert into TEST(id) values (gen_id(GEN_TEST, 1));
commit work;
insert into TEST (id) select gen_id(GEN_TEST, 1) from TEST where gen_id(GEN_TEST, 0) < 2000;
commit work;
Submitted by: @dyemanov
Is related to QA164
When you have a join and a WHERE clause containing an EXISTS predicate that refers to the first joined stream, that EXISTS predicate could be evaluated early (right after the first stream is fetched), but in fact it's evaluated later (after the second stream is fetched) thus not being able to filter the unnecessary rows out from the join in advance and reduce the execution time. This is a regression introduced in v1.5.
Test case:
create table TEST(ID integer not null);
alter table test add constraint PK_TEST primary key (ID);
create generator GEN_TEST;
commit work;
insert into TEST(id) values (gen_id(GEN_TEST, 1));
insert into TEST(id) values (gen_id(GEN_TEST, 1));
commit work;
insert into TEST (id) select gen_id(GEN_TEST, 1) from TEST where gen_id(GEN_TEST, 0) < 2000;
commit work;
-- Now try this statement:
select 1
from TEST T1 join TEST T2 on http://T2.ID >= http://T1.ID
where not exists (select * from TEST T where http://T.ID = http://T1.ID - 1)
and not exists (select * from TEST T where http://T.ID = http://T2.ID + 1)
-- A few thousands of page fetches are okay, while 12 millions are definitely not.
Commits: 914ddbb e93648f
====== Test Details ======
::: NB ::: Test uses EXPLAINED form of plan. Letter from dimitr: 11-mar-2015 19:58.
The text was updated successfully, but these errors were encountered: