CODEWITHSUNDEEP
sqloracle-databasequery-optimization
SGodoy
SGodoy

Reputation: 763

Optimize a heavy query with multiple joins and unions

Are there any optimization tricks for this query to reduce the execution time, which is pretty long. Maybe converting the UNION to JOIN or using UNION ALL?

SELECT COUNT(*) AS "TOTAL"
FROM
    ADS A
    JOIN USERS U ON U.ID = A.USERID
    JOIN LOGIN L ON L.USERID = U.ID
    JOIN (
        SELECT * FROM (
            SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM HOMES T
            UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM CHALETS T
            UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM COUNTRYHOUSES T
            UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM OFFICES T
            UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM BUILDINGS T
            UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM WAREHOUSES T
        ) T
        WHERE
            T.CERTIFICATIONID IN (NULL, 9, 10)
            OR (T.CERTIFICATIONID != 1 AND T.CERTIFICATIONEMISSIONSID IS NULL)
    ) CERT ON CERT.ADID = A.ID
WHERE U.USERTYPE = 1

Upvotes: 0

Views: 139

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Try this:

with CERT as (
    SELECT--+ no_merge
       ADDID, count(*) cnt
    FROM (
        SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM HOMES T
        UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM CHALETS T
        UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM COUNTRYHOUSES T
        UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM OFFICES T
        UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM BUILDINGS T
        UNION SELECT T.ADID, T.CERTIFICATIONID, T.CERTIFICATIONEMISSIONSID FROM WAREHOUSES T
    ) T
    WHERE T.CERTIFICATIONID != 1
    and (T.CERTIFICATIONID IN (9, 10) OR T.CERTIFICATIONEMISSIONSID IS NULL)
    group by ADDID
)
SELECT sum(cnt) AS "TOTAL"
FROM
    ADS A
    JOIN USERS U ON U.ID = A.USERID
    JOIN LOGIN L ON L.USERID = U.ID
    JOIN CERT  ON CERT.ADID = A.ID
WHERE U.USERTYPE = 1

Upvotes: 1

Related Questions