Дано:
FB 1.5.3
Таблица:
Код: Выделить всё
CREATE TABLE TB1 (
RID INTEGER NOT NULL PRIMARY KEY ,
UID INTEGER,
F1 VARCHAR(10),
F2 VARCHAR(10),
V1 INTEGER,
V2 INTEGER
);
Код: Выделить всё
CREATE PROCEDURE CREATE_TEST
AS
DECLARE VARIABLE PID INTEGER;
DECLARE VARIABLE PUID INTEGER;
DECLARE VARIABLE PV1 INTEGER;
DECLARE VARIABLE PV2 INTEGER;
begin
/* Procedure Text */
pid=0;
puid=0;
pv1=0;
pv2=0;
while (:pid<=10000) do
begin
pid=gen_id(gen_rec_id,1);
if (puid=0)
then
begin
insert
into tb1 (rid,uid,f1,f2,v1,v2)
values (:pid, :puid,'a'||cast(:pv1 as varchar(9)),'fff',:pv1+1,:pv2+1);
puid=1;
end
else
begin
insert
into tb1 (rid,uid,f1,f2,v1,v2)
values (:pid, :puid,'ccc','e'||cast(:pv2 as varchar(9)),:pv1+1,:pv2+1);
puid=0;
end
pv1=pv1+1;
pv2=pv2+1;
end
exit;
end
Необходимо выбрать записи с уникальными значениями по UID, где значения V1 и V2 имеют максимальные значения
Используется запрос
Код: Выделить всё
select t1.rid, t1.uid, t1.f1,t1.f2,t1.v1,t1.v2
from tb1 t1
where (select t2.uid
from tb1 t2
group by uid
having t1.v1=max(t2.v1) and t1.v2=max(t2.v2) and t1.uid=t2.uid
) is not null
PLAN SORT ((T2 NATURAL))
PLAN (T1 NATURAL)
Адаптированный план
PLAN SORT ((T2 NATURAL)) PLAN (T1 NATURAL)
------ Performance info ------
Prepare time = 0ms
Execute time = 6m 40s 390ms
Avg fetch time = 66 731,67 ms
Current memory = 2 989 108
Max memory = 3 124 004
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 201 950 497
Также пробовал использовать процедуру
Код: Выделить всё
CREATE PROCEDURE PR_TB1
RETURNS (
PRID INTEGER,
PUID INTEGER,
PF1 VARCHAR(10),
PF2 VARCHAR(10),
PV1 INTEGER,
PV2 INTEGER)
AS
DECLARE VARIABLE VUID INTEGER;
DECLARE VARIABLE VV1 INTEGER;
DECLARE VARIABLE VV2 INTEGER;
begin
/* Procedure Text */
for select t1.uid, max(t1.v1), max(t1.v2) from tb1 t1
group by t1.uid into :vuid,:vv1,:vv2
do
begin
select t2.rid, t2.uid,t2.f1,t2.f2,t2.v1,t2.v2 from tb1 t2
where t2.uid=:vuid and t2.v1=:vv1 and t2.v2=:vv2
into :prid,:puid,:pf1,:pf2,:pv1,:pv2;
suspend;
end
end
Prepare time = 47ms
Execute time = 125ms
Avg fetch time = 20,83 ms
Current memory = 2 998 628
Max memory = 3 143 032
Memory buffers = 10 240
Reads from disk to cache = 0
Writes from cache to disk = 3
Fetches from cache = 141 345