Может ли кто-нибудь сказать мне, как я могу начать оптимизировать этот запрос или порекомендовать что-нибудь
SELECT t1.id
,(
SELECT
CASE WHEN COUNT(s1.id) > 0 THEN 'Y' ELSE 'N' END GIANT
FROM ccp.comms_matrices s1
WHERE (s1.id = NVL(t1.comms_matrix_id, 0))
AND (s1.iod_id IS NOT NULL)
AND (s1.iod_vid IS NOT NULL)
AND (s1.environment_id IS NOT NULL)
AND (s1.giant_url IS NOT NULL)
) GIANT
,t1.exemption_type_id
,t1.market_id
,t8.filename
,t1.service_domain_id
,NVL(t1.service_id, 0) SERVICE_ID
,NVL(t1.demand_ref, 'None') DEMAND_REF
,t1.NAME SUMMARY
,NVL(t1.comms_matrix_id, 0) COMMS_MATRIX_ID
,t1.created_by
,t1.requestor
,t1.known_contact
,t1.updated_by
,t1.ts_created
,t1.business_priority_id
,t1.exemption_status_id
,t7.name exemption_status
,t1.expiry_date
,t1.hits
,CASE
WHEN t1.final_approver IS NOT NULL
THEN 'Not required'
ELSE t1.security_engineer
END GTS_SECURITY_ENGINEER
,CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'Not Applicable'
WHEN t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NOT NULL
AND t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer IS NULL
THEN 'Not Assigned'
ELSE t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer
END TSO_SECURITY_ENGINEER
,t1.description
,t1.notes
,t9.notes || t10.notes || t11.notes || t12.notes || t13.notes || t15.notes || t16.notes || t17.notes || t18.notes || t29.notes tso_notes
,t1.designdoc
,t2.NAME EXEMPTION_TYPE
,t3.NAME MARKET
,t4.NAME SERVICE_DOMAIN
,NVL(t5.NAME, 'All') SERVICE
,t6.NAME BUSINESS_PRIORITY
,t7.NAME STATUS
,NVL(t8.filename, 'All') COMMS_MATRIX
,NVL(t8.uploaded_by, 'None') UPLOADED_BY
,t8.frozen
,CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'group'
WHEN t9.id IS NOT NULL
THEN 'gr'
WHEN t10.id IS NOT NULL
THEN 'pt'
WHEN t11.id IS NOT NULL
THEN 'nl'
WHEN t12.id IS NOT NULL
THEN 'it'
WHEN t13.id IS NOT NULL
THEN 'hu'
WHEN t15.id IS NOT NULL
THEN 'ie'
WHEN t16.id IS NOT NULL
THEN 'es'
WHEN t17.id IS NOT NULL
THEN 'al'
WHEN t18.id IS NOT NULL
THEN 'cz'
WHEN t29.id IS NOT NULL
THEN 'uk'
ELSE 'group'
END local_market
,CASE
WHEN t1.processed IS NULL
AND t1.exemption_status_id = 3
THEN 'Full'
WHEN t1.processed IS NULL
AND (
t1.exemption_status_id = 4
OR t1.exemption_status_id = 5
)
THEN 'Reject'
ELSE t1.processed
END gts_processed
,CASE
WHEN (t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NULL)
OR (t9.processed || t10.processed || t11.processed || t12.processed || t13.processed || t15.processed || t16.processed || t17.processed || t18.processed || t29.processed IS NULL)
THEN 'N/A'
ELSE t9.processed || t10.processed || t11.processed || t12.processed || t13.processed || t15.processed || t16.processed || t17.processed || t18.processed || t29.processed
END tso_processed
,LISTAGG(t14.email, ';') WITHIN
GROUP (
ORDER BY t14.email
) notify_users
,t1.id || NVL(t8.filename, 'All') || NVL(t1.demand_ref, 'None') || t1.NAME || t1.created_by || t1.requestor || t1.known_contact || t1.security_engineer || CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'Not Applicable'
WHEN t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NOT NULL
AND t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer IS NULL
THEN 'Not Assigned'
ELSE t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer
END || t2.NAME || t3.NAME || t4.NAME || NVL(t5.NAME, 'All') || t6.NAME || t7.NAME S,
t19.end_time gts_processed_date,
t1.final_approver,
t20.end_time || t21.end_time || t22.end_time || t23.end_time || t24.end_time || t25.end_time || t26.end_time || t27.end_time || t28.end_time || t30.end_time tso_processed_date
FROM (
SELECT id
,exemption_type_id
,market_id
,service_domain_id
,NVL(service_id, 0) SERVICE_ID
,NVL(demand_ref, 'None') DEMAND_REF
,NAME
,NVL(comms_matrix_id, 0) COMMS_MATRIX_ID
,requestor
,known_contact
,business_priority_id
,exemption_status_id
,expiry_date
,hits
,created_by
,updated_by
,TS_CREATED
,NVL(security_engineer, 'Not Assigned') SECURITY_ENGINEER
,description
,notes
,designdoc
,processed
,final_approver
FROM CCP.exemptions
) t1
INNER JOIN CCP.exemption_types t2 ON (t1.exemption_type_id = t2.id)
INNER JOIN CCP.markets t3 ON (t1.market_id = t3.id)
INNER JOIN CCP.service_domains t4 ON (t1.service_domain_id = t4.id)
LEFT JOIN CCP.services t5 ON (t1.service_id = t5.id)
INNER JOIN CCP.sys_list_business_priority t6 ON (t1.business_priority_id = t6.id)
INNER JOIN CCP.sys_list_exemption_status t7 ON (t1.exemption_status_id = t7.id)
LEFT JOIN CCP.comms_matrices t8 ON (t1.comms_matrix_id = t8.id)
LEFT JOIN CCP.exemptions_gr t9 ON (t1.id = t9.id)
LEFT JOIN CCP.exemptions_pt t10 ON (t1.id = t10.id)
LEFT JOIN CCP.exemptions_nl t11 ON (t1.id = t11.id)
LEFT JOIN CCP.exemptions_it t12 ON (t1.id = t12.id)
LEFT JOIN CCP.exemptions_hu t13 ON (t1.id = t13.id)
LEFT JOIN CCP.exemptions_ie t15 ON (t1.id = t15.id)
LEFT JOIN CCP.exemptions_es t16 ON (t1.id = t16.id)
LEFT JOIN CCP.exemptions_al t17 ON (t1.id = t17.id)
LEFT JOIN CCP.exemptions_cz t18 ON (t1.id = t18.id)
LEFT JOIN CCP.exemptions_uk t29 ON (t1.id = t29.id)
LEFT JOIN ccp.notify_users t14 ON (t14.type_id = t1.id) AND (t14.type="exemption")
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions s1
LEFT JOIN ccp.audit_exemptions s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
AND s2.ts_created > s1.ts_created
GROUP BY s1.id
,s1.ts_created
) r1
) t19 on t1.id = t19.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_gr s1
LEFT JOIN ccp.audit_exemptions_gr s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t20 on t1.id = t20.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_pt s1
LEFT JOIN ccp.audit_exemptions_pt s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t21 on t1.id = t21.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_nl s1
LEFT JOIN ccp.audit_exemptions_nl s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t22 on t1.id = t22.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_it s1
LEFT JOIN ccp.audit_exemptions_it s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t23 on t1.id = t23.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_hu s1
LEFT JOIN ccp.audit_exemptions_hu s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t24 on t1.id = t24.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_ie s1
LEFT JOIN ccp.audit_exemptions_ie s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t25 on t1.id = t25.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_es s1
LEFT JOIN ccp.audit_exemptions_es s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t26 on t1.id = t26.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_al s1
LEFT JOIN ccp.audit_exemptions_al s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t27 on t1.id = t27.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM ccp.exemptions_cz s1
LEFT JOIN ccp.audit_exemptions_cz s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t28 on t1.id = t28.exemption_id
LEFT JOIN (
SELECT r1.exemption_id
,r1.start_time
,r1.end_time
,extract(day FROM (r1.end_time - r1.start_time)) duration
FROM (
SELECT s1.id exemption_id
,s1.ts_created start_time
,max(s2.ts_created) end_time
FROM CCP.exemptions_uk s1
LEFT JOIN ccp.audit_exemptions_uk s2 ON s1.id = s2.table_id
AND s2.action = 'UPDATE'
AND s2.N_processed IN (
'Full'
,'Reject'
)
--AND s2.O_exemption_status_id != s2.n_exemption_status_id
AND s2.ts_created > s1.ts_created
--WHERE s1.id = t1.id
GROUP BY s1.id
,s1.ts_created
) r1
) t30 on t1.id = t30.exemption_id
GROUP BY t1.id
,t1.exemption_type_id
,t1.market_id
,t8.filename
,t1.service_domain_id
,NVL(t1.service_id, 0)
,NVL(t1.demand_ref, 'None')
,t1.NAME
,NVL(t1.comms_matrix_id, 0)
,t1.created_by
,t1.requestor
,t1.known_contact
,t1.updated_by
,t1.ts_created
,t1.business_priority_id
,t1.exemption_status_id
,t7.name
,t1.expiry_date
,t1.hits
,CASE
WHEN t1.final_approver IS NOT NULL
THEN 'Not required'
ELSE t1.security_engineer
END
,CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'Not Applicable'
WHEN t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NOT NULL
AND t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer IS NULL
THEN 'Not Assigned'
ELSE t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer
END
,t1.description
,t1.notes
,t9.notes || t10.notes || t11.notes || t12.notes || t13.notes || t15.notes || t16.notes || t17.notes || t18.notes || t29.notes
,t1.designdoc
,t2.NAME
,t3.NAME
,t4.NAME
,NVL(t5.NAME, 'All')
,t6.NAME
,t7.NAME
,NVL(t8.filename, 'All')
,NVL(t8.uploaded_by, 'None')
,t8.frozen
,CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'group'
WHEN t9.id IS NOT NULL
THEN 'gr'
WHEN t10.id IS NOT NULL
THEN 'pt'
WHEN t11.id IS NOT NULL
THEN 'nl'
WHEN t12.id IS NOT NULL
THEN 'it'
WHEN t13.id IS NOT NULL
THEN 'hu'
WHEN t15.id IS NOT NULL
THEN 'ie'
WHEN t16.id IS NOT NULL
THEN 'es'
WHEN t17.id IS NOT NULL
THEN 'al'
WHEN t18.id IS NOT NULL
THEN 'cz'
WHEN t29.id IS NOT NULL
THEN 'uk'
ELSE 'group'
END
,CASE
WHEN t1.processed IS NULL
AND t1.exemption_status_id = 3
THEN 'Full'
WHEN t1.processed IS NULL
AND (
t1.exemption_status_id = 4
OR t1.exemption_status_id = 5
)
THEN 'Reject'
ELSE t1.processed
END
,CASE
WHEN (t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NULL)
OR (t9.processed || t10.processed || t11.processed || t12.processed || t13.processed || t15.processed || t16.processed || t17.processed || t18.processed || t29.processed IS NULL)
THEN 'N/A'
ELSE t9.processed || t10.processed || t11.processed || t12.processed || t13.processed || t15.processed || t16.processed || t17.processed || t18.processed || t29.processed
END
,t1.id || NVL(t8.filename, 'All') || NVL(t1.demand_ref, 'None') || t1.NAME || t1.created_by || t1.requestor || t1.known_contact || t1.security_engineer || CASE
WHEN (
t9.id IS NULL
AND t10.id IS NULL
AND t11.id IS NULL
AND t12.id IS NULL
AND t13.id IS NULL
AND t15.id IS NULL
AND t16.id IS NULL
AND t17.id IS NULL
AND t18.id IS NULL
AND t29.id IS NULL
)
OR (
t9.id IS NOT NULL
AND t10.id IS NOT NULL
AND t11.id IS NOT NULL
AND t12.id IS NOT NULL
AND t13.id IS NOT NULL
AND t15.id IS NOT NULL
AND t16.id IS NOT NULL
AND t17.id IS NOT NULL
AND t18.id IS NOT NULL
AND t29.id IS NOT NULL
)
THEN 'Not Applicable'
WHEN t9.id || t10.id || t11.id || t12.id || t13.id || t15.id || t16.id || t17.id || t18.id || t29.id IS NOT NULL
AND t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer IS NULL
THEN 'Not Assigned'
ELSE t9.security_engineer || t10.security_engineer || t11.security_engineer || t12.security_engineer || t13.security_engineer || t15.security_engineer || t16.security_engineer || t17.security_engineer || t18.security_engineer || t29.security_engineer
END || t2.NAME || t3.NAME || t4.NAME || NVL(t5.NAME, 'All') || t6.NAME || t7.NAME,
t19.end_time,
t1.final_approver,
t20.end_time || t21.end_time || t22.end_time || t23.end_time || t24.end_time || t25.end_time || t26.end_time || t27.end_time || t28.end_time || t30.end_time;