beautifier format very slow

[zcy]

when include below contents，beautifier format file very slow，
runing 5-6 min.

FOR v IN (SELECT h.*
FROM (SELECT d.prce_id, d.acti_id, d.code, d.pati, d.light, d.delay
FROM TABLE(CAST(io_acin_record1 AS ty_task_record1)) d, wf_activities a
WHERE d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type IS NULL
UNION
SELECT d.prce_id, d.acti_id acti_id, to_char(dest_id) code, d.pati, d.light, AVG(d.delay) delay
FROM tb_declare_stock_chr e, wf_activities a, (SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.id) = d.code
AND e.status = 1
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 1
GROUP BY d.prce_id, d.acti_id, to_char(dest_id), d.pati, d.light
UNION
SELECT d.prce_id, d.acti_id acti_id, to_char(stpr_id) code, d.pati, d.light, AVG(delay) delay
FROM tb_stock_project_bundle_chr e,
wf_activities a,
(SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.desc_id) = d.code
AND e.status = 1
AND e.acti_id IS NULL
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 2
GROUP BY d.prce_id, d.acti_id, to_char(stpr_id), d.pati, d.light
UNION
SELECT DISTINCT d.prce_id, d.acti_id acti_id, to_char(gather_id) code, d.pati, d.light, AVG(delay) delay
FROM tb_budget_audit e, wf_activities a, (SELECT prce_id, acti_id, code, pati, light, delay FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.id) = d.code
AND e.status = 1
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 6
GROUP BY d.prce_id, d.acti_id, to_char(gather_id), d.pati, d.light
UNION
SELECT prce_id, acti_id, to_char(stpr_id) code, pati, light, delay
FROM (SELECT DISTINCT d.prce_id, d.acti_id acti_id, stpr_id, to_char(stpr_id || bundle_code) code, d.pati, d.light, AVG(delay) delay
FROM tb_stock_project_bundle_chr e,
wf_activities a,
(SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.desc_id) = d.code
AND e.status = 1
AND e.acti_id IS NULL
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 7
GROUP BY d.prce_id, d.acti_id, stpr_id, to_char(stpr_id || bundle_code), d.pati, d.light)
UNION
SELECT DISTINCT d.prce_id, d.acti_id acti_id, to_char(stpb.before_bull_id) code, d.pati, d.light, AVG(delay) delay
FROM tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.desc_id) = d.code
AND e.status = 1
AND e.acti_id IS NULL
AND e.stpr_id = stpb.stpr_id
AND e.bundle_code = stpb.bundle_code
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 8
GROUP BY d.prce_id, d.acti_id, to_char(stpb.before_bull_id), d.pati, d.light
UNION
SELECT prce_id, acti_id, to_char(stpr_id) code, pati, light, delay
FROM (SELECT DISTINCT d.prce_id, d.acti_id acti_id, e.stpr_id, to_char(stpb.bull_id) code, d.pati, d.light, AVG(delay) delay
FROM tb_stock_project_bundle_chr e,
tb_stock_project_bundle stpb,
wf_activities a,
(SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.desc_id) = d.code
AND e.status = 1
AND e.acti_id IS NULL
AND e.stpr_id = stpb.stpr_id
AND e.bundle_code = stpb.bundle_code
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 9
GROUP BY d.prce_id, d.acti_id, e.stpr_id, to_char(stpb.bull_id), d.pati, d.light)
UNION
SELECT DISTINCT d.prce_id, d.acti_id acti_id, to_char(bargain_id) code, d.pati, d.light, AVG(delay) delay
FROM tb_bargain_chr e, wf_activities a, (SELECT prce_id, acti_id, code, delay, pati, light FROM TABLE(CAST(io_acin_record1 AS ty_task_record1))) d
WHERE to_char(e.desc_id) = d.code
AND e.status = 1
AND d.prce_id = a.prce_id
AND d.acti_id = a.id
AND a.activity_type = 10
GROUP BY d.prce_id, d.acti_id, to_char(bargain_id), d.pati, d.light) h) LOOP
v_return.extend;
v_return(v_return.last) := ty_watch_resource(i_resource_id,
0,
0,
0,
0,
round(v.delay / v_acti_overtime_unit, 2),
v.pati,
v.code,
szzc_task.f_get_resocuce_code_name(i_resource_id, v.code),
v.light);
END LOOP;

 

[Marco Kalter]

This is indeed unpleasantly slow. We'll fix it.