优化联合所有SQL [英] Optimize Union All SQL
问题描述
您能帮助我优化以下所有联合查询"吗? 知道从哪里开始吗? 细节: SQL计划显示
Could you help me optimize the following Union All Query? Any idea where to start? Details: SQL Plan shows
| 7 |表访问已满| DELAY_DATE | 17533 | 171K | 25(0)| 00:00:01 |
| 8 |表访问已满|空缺| 25879 | 454K | 298(1)| 00:00:04 |
| 7 | TABLE ACCESS FULL | DELAY_DATE | 17533 | 171K| 25 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | VACANCY | 25879 | 454K| 298 (1)| 00:00:04 |
| 16 |合并联接笛卡尔| | 17533 | 273K | 28(4)| 00:00:01 |
| 16 | MERGE JOIN CARTESIAN | | 17533 | 273K| 28 (4)| 00:00:01 |
这些潜在的问题吗?
SELECT delay_reason_desc, delay_start_date, delay_end_date, delay_reason_code
FROM delay_date dd
LEFT JOIN delay_reason dr
ON dr.delay_reason_id=dd.delay_reason_id
LEFT JOIN delay_interval di
ON di.delay_interval_id=dr.delay_interval_id
LEFT JOIN vacancy v
ON v.vacancy_id=dd.vacancy_id
WHERE dr.delay_reason_active_ind='Y'
--AND dd.vacancy_id=p_vacancy_id
AND delay_start_date >= vacancy_fpps_received_date
AND delay_end_date <= vacancy_open_date
AND delay_interval_code=1
UNION ALL
SELECT delay_reason_desc, delay_start_date, delay_end_date, delay_reason_code
FROM delay_date dd
LEFT JOIN delay_reason dr
ON dr.delay_reason_id=dd.delay_reason_id
LEFT JOIN delay_interval di
ON di.delay_interval_id=dr.delay_interval_id
LEFT JOIN vacancy v
ON v.vacancy_id=dd.vacancy_id
WHERE dr.delay_reason_active_ind='Y'
--AND dd.vacancy_id=p_vacancy_id
AND delay_start_date >= vacancy_open_date
AND delay_end_date <= vacancy_closed_date
AND delay_interval_code=2
推荐答案
如何解决-可能快两倍?
How about this -- could be as much as twice as fast:
SELECT delay_reason_desc, delay_start_date, delay_end_date, delay_reason_code
FROM delay_date dd
LEFT JOIN delay_reason dr
ON dr.delay_reason_id=dd.delay_reason_id
LEFT JOIN delay_interval di
ON di.delay_interval_id=dr.delay_interval_id
LEFT JOIN vacancy v
ON v.vacancy_id=dd.vacancy_id
WHERE dr.delay_reason_active_ind='Y'
AND ((delay_start_date >= vacancy_fpps_received_date
AND delay_end_date <= vacancy_open_date
AND delay_interval_code=1)
OR (delay_start_date >= vacancy_open_date
AND delay_end_date <= vacancy_closed_date
AND delay_interval_code=2))
这篇关于优化联合所有SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!