带有 CASE WHEN EXISTS 子查询优化的 Oracle SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33828311/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL query with CASE WHEN EXISTS subquery optimization
提问by Cheshire Cat
I'm using the following query to create a view in Oracle 11g (11.2.0.3.0).
我正在使用以下查询在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;
Then I query that view to get the values for a DataGridViewin an ASPXpage.
然后我查询这种观点得到值一DataGridView中的ASPX网页。
SELECT *
FROM V_DOCUMENTS_LIST
ORDER BY ID_STATE DESC, INSTITUTE, INSERT_DATE DESC;
Relevant tables and relations
相关表和关系
DOCUMENTS; FILES; JOBS;
文件；文件；工作;
DOCUMENTS (1-1) <----> (0-N) FILES
文件 (1-1) <----> (0-N) 文件
JOBS (0-1) <----> (0-N) DOCUMENTS
工作 (0-1) <----> (0-N) 文件
Querying the view I get the complete list of documents with all their associated information (ID, description, dates, state, etc.) and also for each one:
查询视图，我得到完整的文档列表及其所有相关信息（ID、描述、日期、状态等）以及每个文档：
- total count of attached files;
- total dimension in bytes of attached files;
- boolean value indicating whether there's at least one
JOBassociated to theDOCUMENTor not.
- 附件总数；
- 附件的总维度（以字节为单位）；
- 布尔值，指示是否有至少一个
JOB关联到DOCUMENT或没有。
Everything worked fine untile the view contained a few thousand records. Now the records amount is increasing and the SELECT * FROMon the view takes about 2:30 mins with 15.000-20.000 records.
I know that a really time consuming part of my view is the nested SELECT:
一切正常，直到视图包含几千条记录。现在记录数量正在增加，并且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
How can I optimize my view?
如何优化我的视图？
回答by Florin Ghita
To address the not exists issue, you can add a join:
要解决不存在的问题，您可以添加一个联接：
LEFT JOIN (select distinct id_doc from JOB) J
ON d.id_doc = J.id_doc
The Has_job column would be:
Has_job 列将是：
CASE
WHEN j.id_doc is not null THEN 'true'
ELSE 'false'
END AS HASJOB
PS: Your current implementation has a problem, as SELECT D.ID_DOC FROM JOBwould allways contain rows if job table has rows. It is equivalent with select * from job, because exists just test existence of rows. A logically correct implementation would be: SELECT 1 FROM JOB j where j.id_doc = D.ID_DOC.
PS：您当前的实现有问题，因为SELECT D.ID_DOC FROM JOB如果作业表有行，则总是包含行。它等同于select * from job，因为存在只是测试行的存在。逻辑上正确的实现将是：SELECT 1 FROM JOB j where j.id_doc = D.ID_DOC。
回答by Iffo
You are going full index on table JOB, put WHEREclause in the query:
您将在 table 上进行完整索引JOB，WHERE在查询中放置子句：
SELECT D.ID_DOC FROM JOB
