The below original query takes 6 seconds for execution. But when I remove most of select union from dual in joint statement (just keep 1) then the query just takes 1.2 seconds for execution. Can someone tell me what the issue is and how to fix this?
Original Query:
SELECT DISTINCT b.RNUM as ROW_NUM,
b.MAX_ROWNUM,
b.ta_or_od_id,
b.lnk_od_id,
b.bussiness_unit,
b.CIF,
b.pf_id,
b.grp_tsk_status,
b.grp_tsk_type_and_status,
ta.src_sys,
ta.DUE_DATE_VALUE,
ta.mod_at,
tr.ORDER_ID
FROM (SELECT 1 AS RNUM,
'ABC345IABX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00019' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 2 AS RNUM,
'ABC345IACX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00020' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla1|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 3 AS RNUM,
'ABC345IADX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00021' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla2|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 4 AS RNUM,
'ABC345IAEX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00022' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla3|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 5 AS RNUM,
'ABC345IAFX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00023' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla4|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 6 AS RNUM,
'ABC345IAGX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00024' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla5|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 7 AS RNUM,
'ABC345IAHX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00025' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla6|Open' AS grp_tsk_type_and_status
FROM DUAL) b
JOIN tsk_ref tr on case
when b.lnk_od_id is not null and regexp_like(b.lnk_od_id,
'^' || tr.od_id ||
'_|_' || tr.od_id || '$')
then 1
when b.lnk_od_id is null and
nvl(tr.od_id, tr.tsk_id) = b.ta_or_od_id then 1
else 0 end = 1 AND b.bussiness_unit = tr.bussiness_unit AND
NVL(b.pf_id, 'NULL') = NVL(tr.pf_id, 'NULL')
JOIN tsk_act ta ON tr.tsk_id = ta.tsk_id
LEFT OUTER JOIN rt_saving_cf rsc
ON rsc.rul_id = tr.rul_id AND (rsc.chn = tr.chn OR rsc.chn = 'ALL') AND
rsc.ex_only = ta.so AND rsc.act = 'Y'
WHERE tr.tsk_id IN (SELECT DISTINCT sl_id
FROM (SELECT tr.tsk_id, tr.pr_tsk_id
FROM (SELECT 1 AS RNUM,
'ABC345IABX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00019' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 2 AS RNUM,
'ABC345IACX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00020' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla1|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 3 AS RNUM,
'ABC345IADX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00021' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla2|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 4 AS RNUM,
'ABC345IAEX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00022' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla3|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 5 AS RNUM,
'ABC345IAFX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00023' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla4|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 6 AS RNUM,
'ABC345IAGX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00024' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla5|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 7 AS RNUM,
'ABC345IAHX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00025' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla6|Open' AS grp_tsk_type_and_status
FROM DUAL) b join tsk_ref tr on case when b.lnk_od_id is not null and
regexp_like(b.lnk_od_id,
'^' || tr.od_id || '_|_' ||
tr.od_id || '$') then 1
when b.lnk_od_id is null and
nvl(tr.od_id, tr.tsk_id) = b.ta_or_od_id
then 1
else 0 end = 1 AND b.bussiness_unit = tr.bussiness_unit AND
NVL(b.pf_id, 'NULL') = NVL(tr.pf_id, 'NULL')
JOIN tsk_act ta ON tr.tsk_id = ta.tsk_id
WHERE TA.tsk_status IN ('Open', 'Rejected')
AND UPPER(TR.pr_id) IN ('MA179234')) UNPIVOT (sl_id FOR COL IN (tsk_id, pr_id)))
ORDER BY b.RNUM, b.ta_or_od_id, ta.DUE_DATE_VALUE ASC NULLS LAST, ta.mod_at DESC;
Query after removing most of select union from dual in join statement:
SELECT DISTINCT b.RNUM as ROW_NUM,
b.MAX_ROWNUM,
b.ta_or_od_id,
b.lnk_od_id,
b.bussiness_unit,
b.CIF,
b.pf_id,
b.grp_tsk_status,
b.grp_tsk_type_and_status,
ta.src_sys,
ta.DUE_DATE_VALUE,
ta.mod_at,
tr.ORDER_ID
FROM (SELECT 1 AS RNUM,
'ABC345IABX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00019' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 2 AS RNUM,
'ABC345IACX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00020' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla1|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 3 AS RNUM,
'ABC345IADX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00021' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla2|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 4 AS RNUM,
'ABC345IAEX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00022' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla3|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 5 AS RNUM,
'ABC345IAFX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00023' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla4|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 6 AS RNUM,
'ABC345IAGX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00024' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla5|Open' AS grp_tsk_type_and_status
FROM DUAL
UNION
SELECT 7 AS RNUM,
'ABC345IAHX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00025' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla6|Open' AS grp_tsk_type_and_status
FROM DUAL) b
JOIN tsk_ref tr on case
when b.lnk_od_id is not null and regexp_like(b.lnk_od_id,
'^' || tr.od_id ||
'_|_' || tr.od_id || '$')
then 1
when b.lnk_od_id is null and
nvl(tr.od_id, tr.tsk_id) = b.ta_or_od_id then 1
else 0 end = 1 AND b.bussiness_unit = tr.bussiness_unit AND
NVL(b.pf_id, 'NULL') = NVL(tr.pf_id, 'NULL')
JOIN tsk_act ta ON tr.tsk_id = ta.tsk_id
LEFT OUTER JOIN rt_saving_cf rsc
ON rsc.rul_id = tr.rul_id AND (rsc.chn = tr.chn OR rsc.chn = 'ALL') AND
rsc.ex_only = ta.so AND rsc.act = 'Y'
WHERE tr.tsk_id IN (SELECT DISTINCT sl_id
FROM (SELECT tr.tsk_id, tr.pr_tsk_id
FROM (SELECT 1 AS RNUM,
'ABC345IABX' AS ta_or_od_id,
NULL AS lnk_od_id,
'0001' AS bussiness_unit,
'00019' AS CIF,
NULL AS pf_id,
7 AS MAX_ROWNUM,
'Open' AS grp_tsk_status,
'Tesla|Open' AS grp_tsk_type_and_status
FROM DUAL) b join tsk_ref tr on case when b.lnk_od_id is not null and
regexp_like(b.lnk_od_id,
'^' || tr.od_id || '_|_' ||
tr.od_id || '$') then 1
when b.lnk_od_id is null and
nvl(tr.od_id, tr.tsk_id) = b.ta_or_od_id
then 1
else 0 end = 1 AND b.bussiness_unit = tr.bussiness_unit AND
NVL(b.pf_id, 'NULL') = NVL(tr.pf_id, 'NULL')
JOIN tsk_act ta ON tr.tsk_id = ta.tsk_id
WHERE TA.tsk_status IN ('Open', 'Rejected')
AND UPPER(TR.pr_id) IN ('MA179234')) UNPIVOT (sl_id FOR COL IN (tsk_id, pr_id)))
ORDER BY b.RNUM, b.ta_or_od_id, ta.DUE_DATE_VALUE ASC NULLS LAST, ta.mod_at DESC;
I would like to speed up the query.
Simplify with a Common Table Expression
Before you start tuning, simplify the query with a common table expression (CTE). Also, replace
UNIONwithUNION ALLwhenever you know the rows are unique, to avoid sorting. The below code only puts the new rows in a CTE, but your real version should also include the duplicate joins in the CTE.Temp table
When I said temp table in my comment, I was referring to the temp table that Oracle will likely automatically create for you, as seen in the beginning of this explain plan:
It may be a good idea to create an Oracle global temporary table instead, but that decision depends on several factors. A global temporary table requires more work to manage the object, including dealing with statistics on a rapidly changing object. On the plus side, the query will always be the same, which reduces parsing time and makes the query easier to manage and tune.
If this query only runs once an hour, and the CTE is good enough to help with performance, then it's fine to hard code everything. If the query runs 10 times a second, and creates persistent performance problems, then you should probably spend the time to make the global temporary table. (Or, alternatively, you could use an oracle type and collection to pass in multiple rows of data.)
Tuning
When you've simplified and narrowed down the choices, you'll want to tune by generating an execution plan with the real numbers. I recommend either the
GATHER_PLAN_STATISTICSorDBMS_SQLTUNE.REPORT_SQL_MONITOR. Those two tools produce much more useful results than a mere explain plan. Edit your question with the results and we can take another look at the problem.