一下SQL有什么优化方法？谢谢。

unusualmachine 2009-05-16 06:31:07

/* Formatted on 2009/05/16 17:49 (Formatter Plus v4.8.5) */
(SELECT uen_dps.uen, com.NAME, uen_invol.invol, uen_dps.workplace_no,
uen_dps.dps, uen_warn.warn_action_dt AS warn_action_dt,
uen_bl.dp_action_dt AS dp_action_dt, ROWNUM
FROM (SELECT uen, workplace_no,
REPLACE (wmsys.wm_concat (dps),
',',
' ' || CHR (10) || CHR (13)
) AS dps
FROM pen_dp_enq_wp_pnt
GROUP BY uen, workplace_no) uen_dps,
(SELECT warn.uen AS uen, warn.warn_action_dt AS warn_action_dt
FROM pen_dp_warn warn,
(SELECT uen, MAX (warn_action_dt) AS warn_action_dt
FROM pen_dp_warn
WHERE ADD_MONTHS (warn_action_dt, 12) > SYSTIMESTAMP
GROUP BY uen) temp_warn,
(SELECT uen
FROM pen_dp_summary
WHERE delete_ind = 'F'
AND (current_stage != '0' OR current_stage != '1')) summary
WHERE warn.uen = temp_warn.uen
AND warn.warn_action_dt = temp_warn.warn_action_dt
AND warn.uen = summary.uen) uen_warn,
(SELECT bl.uen, bl.workplace_no, bl.dp_action_dt AS dp_action_dt
FROM pen_bl bl,
(SELECT uen, workplace_no,
MAX (dp_action_dt) AS dp_action_dt
FROM pen_bl
WHERE dp_ind = 'T' AND end_dt > SYSTIMESTAMP
GROUP BY uen, workplace_no) temp_bl,
(SELECT uen
FROM pen_dp_summary
WHERE delete_ind = 'F'
AND (current_stage != '0' OR current_stage != '1')) summary
WHERE bl.uen = temp_bl.uen
AND temp_bl.workplace_no = bl.workplace_no
AND bl.dp_action_dt = temp_bl.dp_action_dt
AND bl.uen = summary.uen) uen_bl,
(SELECT DISTINCT event.uen, event.invol, item.workplace_no
FROM pen_dp_event event, pen_dp_item item
WHERE event.dp_event_id = item.dp_event_id) uen_invol,
entp_company com
WHERE uen_warn.uen = uen_dps.uen
AND uen_bl.uen = uen_dps.uen
AND uen_bl.workplace_no = uen_dps.workplace_no
AND uen_dps.uen = uen_invol.uen
AND NVL (uen_dps.workplace_no, 0) = NVL (uen_invol.workplace_no, 0)
AND com.uen = uen_dps.uen
AND ({?PARM_DP_STATUS} = 'Active' OR {?PARM_DP_STATUS} = 'Blacklisted')
UNION ALL
SELECT uen_dps.uen, com.NAME, uen_invol.invol, uen_dps.workplace_no,
uen_dps.dps, uen_warn.warn_action_dt AS warn_action_dt,
NULL AS dp_action_dt, ROWNUM
FROM (SELECT uen, workplace_no,
REPLACE (wmsys.wm_concat (dps),
',',
' ' || CHR (10) || CHR (13)
) AS dps
FROM pen_dp_enq_wp_pnt
GROUP BY uen, workplace_no) uen_dps,
(SELECT warn.uen AS uen, warn.warn_action_dt AS warn_action_dt
FROM pen_dp_warn warn,
(SELECT uen, MAX (warn_action_dt) AS warn_action_dt
FROM pen_dp_warn
WHERE ADD_MONTHS (warn_action_dt, 12) > SYSTIMESTAMP
GROUP BY uen) temp_warn,
(SELECT uen
FROM pen_dp_summary
WHERE current_stage = '2A' AND delete_ind = 'F') summary
WHERE warn.uen = temp_warn.uen
AND warn.warn_action_dt = temp_warn.warn_action_dt
AND warn.uen = summary.uen) uen_warn,
(SELECT DISTINCT event.uen, event.invol, item.workplace_no
FROM pen_dp_event event, pen_dp_item item
WHERE event.dp_event_id = item.dp_event_id
AND event.delete_ind = 'F'
AND item.delete_ind = 'F') uen_invol,
entp_company com
WHERE uen_dps.uen = uen_invol.uen
AND NVL (uen_dps.workplace_no, 0) = NVL (uen_invol.workplace_no, 0)
AND com.uen = uen_dps.uen
AND uen_warn.uen = uen_dps.uen
AND com.delete_ind = 'F'
AND ({?PARM_DP_STATUS} = 'Active' OR {?PARM_DP_STATUS} = 'Warned'))

...全文