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
create table t2 (
id int not null,
t1_id int not null
);
set term ^;
execute block
as
declare i int;
begin
i = 1;
while (i <= 100) do
begin
insert into t1(id) values(:i);
i = i + 1;
end
i = 1;
while (i < 1000000) do
begin
insert into t2(id, t1_id) values(:i, :i / 100000 + 1);
i = i + 1;
end
end^
set term ;^
commit;
alter table t1
add constraint pk_t1 primary key(id);
alter table t2
add constraint pk_t2 primary key(id);
alter table t2
add constraint fk_t2_t1 foreign key(t1_id) references t1(id);
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Access By ID
-> Index "PK_T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan
MIN
============
1
Current memory = 11840168
Delta memory = 1672
Max memory = 11872248
Elapsed time= 0.058 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 38 <-- good
Select Expression
-> First N Records
-> Nested Loop Join (inner)
-> Table "T1" Access By ID
-> Index "PK_T1" Full Scan
-> Filter
-> Table "T2" Access By ID
-> Bitmap
-> Index "FK_T2_T1" Range Scan (full match)
ID
============
1
Current memory = 11933608
Delta memory = 52288
Max memory = 11933760
Elapsed time= 0.062 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 137 <-- good
The text was updated successfully, but these errors were encountered:
Submitted by: @sim1984
Is related to CORE1482
Duplicates CORE4798
it's extended ticket CORE1482
create table t1 (
id int not null
);
create table t2 (
id int not null,
t1_id int not null
);
set term ^;
execute block
as
declare i int;
begin
i = 1;
while (i <= 100) do
begin
insert into t1(id) values(:i);
i = i + 1;
end
i = 1;
while (i < 1000000) do
begin
insert into t2(id, t1_id) values(:i, :i / 100000 + 1);
i = i + 1;
end
end^
set term ;^
commit;
alter table t1
add constraint pk_t1 primary key(id);
alter table t2
add constraint pk_t2 primary key(id);
alter table t2
add constraint fk_t2_t1 foreign key(t1_id) references t1(id);
select min(http://t1.id)
from t1
join t2 on http://t1.id = t2.t1_id;
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan
============
1
Current memory = 11621440
Delta memory = 547712
Max memory = 11672536
Elapsed time= 3.302 sec
Buffers = 2048
Reads = 11421
Writes = 0
Fetches = 6026958 <-- no good
vs
select min(http://t2.id)
from t1
join t2 on http://t1.id = t2.t1_id;
Select Expression
-> Aggregate
-> Nested Loop Join (inner)
-> Table "T2" Access By ID
-> Index "PK_T2" Full Scan
-> Filter
-> Table "T1" Access By ID
-> Bitmap
-> Index "PK_T1" Unique Scan
============
1
Current memory = 11840168
Delta memory = 1672
Max memory = 11872248
Elapsed time= 0.058 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 38 <-- good
vs
same as 1 query, but much faster
select http://t1.id
from t1
join t2 on http://t1.id = t2.t1_id
order by http://t1.id rows 1;
Select Expression
-> First N Records
-> Nested Loop Join (inner)
-> Table "T1" Access By ID
-> Index "PK_T1" Full Scan
-> Filter
-> Table "T2" Access By ID
-> Bitmap
-> Index "FK_T2_T1" Range Scan (full match)
============
1
Current memory = 11933608
Delta memory = 52288
Max memory = 11933760
Elapsed time= 0.062 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 137 <-- good
The text was updated successfully, but these errors were encountered: