我正在使用以下查询在Oracle 11g(11.2.0.3.0)中创建视图。
CREATE OR REPLACE FORCE VIEW V_DOCUMENTS_LIST
(
ID_DOC,
ATTACHMENTS_COUNT,
TOTAL_DIMENSION,
INSERT_DATE,
ID_STATE,
STATE,
ID_INSTITUTE,
INSTITUTE,
HASJOB
)
AS
SELECT D.ID_DOC,
COUNT (F.ID_FILE) AS ATTACHMENTS_COUNT,
CASE
WHEN SUM (F.DIMENSION) IS NULL THEN 0
ELSE SUM (F.DIMENSION)
END
AS TOTAL_DIMENSION,
D.INSERT_DATE,
D.ID_STATE,
S.STATE_DESC AS STATE,
D.ID_INSTITUTE,
E.NAME AS INSTITUTE,
CASE
WHEN EXISTS (SELECT D.ID_DOC FROM JOB) THEN 'true'
ELSE 'false'
END
AS HASJOB
FROM DOCUMENTS D
LEFT JOIN FILES F ON D.ID_DOC = F.ID_DOC
JOIN STATES S ON D.ID_STATE = S.ID_STATE
JOIN INSTITUTES E ON D.ID_INSTITUTE = E.ID_INSTITUTE
GROUP BY D.ID_DOC,
D.INSERT_DATE,
D.ID_STATE,
S.STATE_DESC,
D.ID_INSTITUTE,
E.NAME;
然后我查询这种观点得到值一DataGridView
中的ASPX
网页。
SELECT *
FROM V_DOCUMENTS_LIST
ORDER BY ID_STATE DESC, INSTITUTE, INSERT_DATE DESC;
相关表和关系
文件;文件;职位;
文件(1-1)<---->(0-N)个文件
职位(0-1)<---->(0-N)文档
查询视图时,我获得了包含所有相关信息(ID,描述,日期,状态等)以及每个文档的完整文档列表:
JOB
关联到 DOCUMENT
或没有。一切正常,直到视图包含数千条记录为止。现在,记录量正在增加,并且SELECT * FROM
视图上需要大约2:30分钟,具有15.000-20.000条记录。我知道嵌套是我视图中真正耗时的部分SELECT
:
CASE
WHEN EXISTS (SELECT D.ID_DOC FROM JOB) THEN 'true'
ELSE 'false'
END
AS HASJOB
如何优化视图?
要解决不存在的问题,可以添加联接:
LEFT JOIN (select distinct id_doc from JOB) J
ON d.id_doc = J.id_doc
Has_job列为:
CASE
WHEN j.id_doc is not null THEN 'true'
ELSE 'false'
END AS HASJOB
PS:您当前的实现有问题,SELECT D.ID_DOC FROM JOB
如果作业表中有行,那么总会包含行。它等效于select * from job
,因为存在仅测试行的存在。逻辑上正确的实现为:SELECT 1 FROM JOB j where j.id_doc = D.ID_DOC
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句