我需要一些帮助来优化此 SQL 查询。这是完全正常的。我只想减少这个查询的运行时间
select distinct
o.usrp_order_number,t.*
from ms_bvoip_order_extension oe
inner join ms_order o on oe.ms_order_id = o.ms_order_id
inner join ms_sub_order so on so.ms_order_id = o.ms_order_id
inner join ms_job j on j.entity_id = so.ms_sub_order_id
left join mstask t ON t.wf_job_id = j.wf_job_id
where
o.order_type = 900
and o.entered_date between date_sub(current_date(),53) and
date_sub(current_date(),3)
and j.entity_type = 5 and t.name RLIKE 'Error|Correct|Create AOTS Ticket' and t.wf_job_id is not null
order by
o.usrp_order_number
在 Hive 中加入后执行 WHERE 条件（尽管 CBO 和 PPD 可能会改变这种行为），更好地研究两个查询的 EXPLAIN 输出。您可以将这样的条件移动o.order_type = 900到 join ON 子句以减少连接时的行数。Hive 的连接 ON 子句中只允许涉及两个表列的非等条件。还表t是左接合，但在条件where：t.name RLIKE 'Error|Correct|Create AOTS Ticket' and t.wf_job_id is null and t.ORIGINAL_START_DATE is not null变换左连接到内连接。检查您是否需要 INNER 或 LEFT JOIN
select distinct
o.usrp_order_number,t.*
from ms_bvoip_order_extension oe
inner join ms_order o
on oe.ms_order_id = o.ms_order_id
and o.order_type = 900
and and o.entered_date between date_sub(current_date(),53) and date_sub(current_date(),3)
inner join ms_sub_order so on so.ms_order_id = o.ms_order_id
inner join ms_job j on j.entity_id = so.ms_sub_order_id
and j.entity_type = 5
left join mstask t on t.wf_job_id = j.wf_job_id
and t.name RLIKE 'Error|Correct|Create AOTS Ticket'
and t.wf_job_id is null
and t.ORIGINAL_START_DATE is not null
order by o.usrp_order_number
另请阅读有关配置设置的答案：https : //stackoverflow.com/a/48487306/2700344
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句