Oracle SQL query with CASE WHEN EXISTS subquery optimization

Question

I'm using the following query to create a view in 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 DataGridView in an ASPX page.

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

JOBS (0-1) <----> (0-N) DOCUMENTS

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:

• total count of attached files;
• total dimension in bytes of attached files;
• boolean value indicating whether there's at least one JOB associated to the DOCUMENT or not.

Everything worked fine untile the view contained a few thousand records. Now the records amount is increasing and the SELECT * FROM on 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:

CASE
    WHEN EXISTS (SELECT D.ID_DOC FROM JOB) THEN 'true'
    ELSE 'false'
END
AS HASJOB

How can I optimize my view?

Seven

Pls share the execution plan with us. – Sorrento

Answer 1

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:

    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 JOB would 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.

Sorrento

Both solutions works well. 1) LEFT JOIN the JOBS table and then use your CASE statement. 2) Keep my CASE statement with your SELECT 1 FROM JOBS J WHERE J.ID_DOC = D.ID_DOC withount joining the JOBS table. Both perform good. Same execution time.Thanks! – Seven

Answer 2

You are going full index on table JOB, put WHERE clause in the query:

SELECT D.ID_DOC FROM JOB

Nuss