有一个要求
select
eq.ID, …, c_CALIBRATION_INTERVAL
from EQUIPMENT eq
left join LABORATORY l on l.id = eq.EQUIPMENT_LABORATORY_ID
left join LABORATORY ld on ld.id = eq.LABORATORY_DOC_ID
left join CERTIFICATE c on c.DEVICE_ID_CL = eq.ID
and c.DISPLAY = 1
left join USERS_DATA u on u.ID = eq.EQUIPMENT_PARENT_USER_ID
left join USERS_DATA ud on ud.ID = eq.USER_DOC_ID
where eq.ARCHIVE in (1)
and (((eq.CONTROL_MIN_DATE < '06.02.2023' and not eq.CONTROL_MIN_DATE is null) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0) or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT))
and ((eq.CONTROL_MIN_DATE < '06.02.2023' and not eq.CONTROL_MIN_DATE is null) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0) or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT)))
and ((c.CONTROL = 1 and eq.PASSPORT = 0) or ((c.CONTROL is null or c.CONTROL=1) and eq.PASSPORT = 1))
order by eq.EQUIPMENT_PARENT_USER_ID, eq.NAME, eq.TYPE, eq.NUMBER, eq.NEMBER_INVENTORY
, 但它几乎可以正常工作，我不得不用一个子查询来补充它，我把它塞进了VIEW：
CREATE OR ALTER VIEW V_CERTIFICATE_SIGNAL(
DEVICE_ID_CL,
CERTIFICATE_ID)
AS
select gr0.DEVICE_ID_CL, (case when gr1.id is null then gr0.id else gr1.id end) as CERTIFICATE_ID
from (select max(c.ID) as ID, c.DEVICE_ID_CL
from CERTIFICATE c
where c.CONTROL = 1
group by c.DEVICE_ID_CL) gr0
left join (select c.id, c.DEVICE_ID_CL
from CERTIFICATE c
where c.CONTROL = 1 and c."DISPLAY" = 1) gr1 on gr0.DEVICE_ID_CL = gr1.DEVICE_ID_CL ;
- CERTIFICATE
表的一部分
分别提出的两个查询都可以快速运行。但是当我将第一个请求与VIEW结合起来时，它开始变慢很多。
select
eq.ID, eq.NAME, eq.CONTROL_MIN_DATE, eq."TYPE", eq.NUMBER, eq.NEMBER_INVENTORY, eq.PRODUCER, eq.YEAR_BEGIN, eq.NAME_TEST, eq.REMARK, eq.USER_ID, eq.DEPARTMENT_ID, eq.TIME_EDIT,
eq.ARCHIVE, eq.SPECIFICATION, eq.DEVICE_DEPARTMENT_ID, eq.TYPE_SERIES, eq.DISLOCATION, eq.TYPE_EQUIP, eq.PASSPORT, eq.WARNING, eq.EQUIPMENT_LABORATORY_ID, eq.COUNT_CERT_REQUIRE,
eq.CONTROL_MIN_DOC, eq.EQUIPMENT_PARENT_USER_ID, eq.COUNT_CERT, l.NAME_SHORT as l_NAME_SHORT_LAB, eq.LABORATORY_DOC_ID, eq.FIND_TALLY, eq.DISLOCATION_ORG, eq.USER_DOC_ID,
u.FULL_NAME as u_EQUIPMENT_USER, ld.NAME_SHORT as l_LABORATORY_DOC, ud.FULL_NAME as u_USER_DOC
, (case when c.NUMBER_CERT = '-1' then '' else c.NUMBER_CERT end) as c_NUMBER_CERT, c.DATE_OPERATION as c_DATE_OPERATION, c.CALIBRATION_INTERVAL as c_CALIBRATION_INTERVAL
from EQUIPMENT eq
left join LABORATORY l on l.id = eq.EQUIPMENT_LABORATORY_ID
left join LABORATORY ld on ld.id = eq.LABORATORY_DOC_ID
left join V_CERTIFICATE_SIGNAL vcs on vcs.DEVICE_ID_CL = eq.ID -- "VIEW"
left join CERTIFICATE c on vcs.CERTIFICATE_ID = c.ID
left join USERS_DATA u on u.ID = eq.EQUIPMENT_PARENT_USER_ID
left join USERS_DATA ud on ud.ID = eq.USER_DOC_ID
where eq.ARCHIVE in (1)
and (((eq.CONTROL_MIN_DATE < '07.02.2023' and not eq.CONTROL_MIN_DATE is null) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0) or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT))
and ((eq.CONTROL_MIN_DATE < '07.02.2023' and not eq.CONTROL_MIN_DATE is null) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT <> -1) or (eq.CONTROL_MIN_DATE is null and eq.COUNT_CERT >= 0) or (eq.COUNT_CERT_REQUIRE <> eq.COUNT_CERT)))
and ((c.CONTROL = 1 and eq.PASSPORT = 0) or ((c.CONTROL is null or c.CONTROL=1) and eq.PASSPORT = 1))
order by eq.EQUIPMENT_PARENT_USER_ID, eq.NAME, eq.TYPE, eq.NUMBER, eq.NEMBER_INVENTORY
大约 30 秒，从可能的
344 条记录中选择 94 条 以常规表格的形式呈现VIEW的结果，并将其替换为VIEW，一切都进行得很快，没有刹车。
使用VIEW可以看到什么以及在哪里挖掘以加速查询？先感谢您。
