OSC 请你来轰趴啦！1028 苏州源创会，一起寻宝 AI 时代
本文分享自华为云社区《GaussDB(DWS)性能调优：实时场景下表行数估算不准确引起的的性能瓶颈问题案例》，作者： O泡果奶~。
本文针对实时场景下SQL语句因表行数估算不准确而导致语句执行超时报错的案例进行分析。
1、【问题描述】
实时场景下，select查询语句执行时间过长，该语句verbose执行计划中存在nestloop，且使用hint(set (enable_index_nestloop off)) 无法生效。
2、【原始语句】
select * from ( select
wo.work_order_id /*工单id*/,
wo.work_order_code /*工单编码*/,
wo.work_order_name /*工单名称*/,
wo.work_order_level /*工单层级(第一层级（未拆分工单/父工单）：1，第二层级(子工单)：10)*/,
decode(wo.work_order_level,1, '第一层级（未拆分工单/父工单）', 10,'第二层级(子工单)') as work_order_level_desc /*工单层级描述*/,
substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/,
wo.wo_version /*工单版本号*/,
wo.wo_lifecycle_status /*生命周期标识：0：正常工单，-1: 已删除*/,
wo.business_id /*工单来源业务id*/,
wo.business_type /*工单来源业务类型（10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10）*/,
decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单',
'70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/,
wo.parent_activity_id /*父节点活动id*/,
wo.activity_lib_id /*活动库活动id*/,
wo.activity_type /*作业类型，1wbs，2活动，3里程碑*/,
ac.activity_name /*活动名称*/,
ac.std_ms_code as standard_ms_code /*标准里程碑编码*/,
wo.plan_id /*计划id*/,
wo.project_number as proj_num /*项目编码*/,
wo.du_id /*交付单元id*/,
wo.duration /*工期*/,
wo.billing_flag /*开票标识:y-开票*/,
wo.na_flag /*na标识*/,
wo.inv_flag /*inv标识*/,
wo.master_flag /*拆分标示，n：未拆分 ; y:已拆分*/,
wo.created_by as created_by_id /*创建人user id*/,
u1.lname as created_by /*创建人*/,
wo.creation_date /*创建时间*/,
wo.last_updated_by as last_updated_by_id /*最后更新人user id*/,
u2.lname as last_updated_by /*最后更新人*/,
wo.last_update_date /*最后更新时间*/,
wp.wo_progress_id /*活动进度id*/,
wp.expect_start_date /*预期开始日期*/,
wp.expect_end_date /*预期结束日期*/,
wp.plan_start_time /*计划开始时间*/,
wp.plan_end_time /*计划完成时间*/,
wp.actual_start_time /*实际开始时间*/,
wp.actual_end_time /*实际完成时间*/,
wp.close_time /*活动关闭时间*/,
wp.completion_rate /*完工比率（数值如 0.8666）*/,
to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/,
wp.total_value /*总值*/,
wp.accumulate_value /*累计值*/,
wp.report_time /*值反馈时间*/,
wp.total_plan_value /*总计划值*/,
wp.ehs_risk /*高危活动类型*/,
wp.delay_reason_id /*延迟原因id*/,
substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/,
wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/,
l2.item_name as wo_status_desc /*活动状态描述*/,(
case
when lengthb(wp.approve_status) = 0 then null
else wp.approve_status
end
) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/,
l3.item_name as approve_status_desc /*审批状态描述*/,
wp.par_workorder_doc_flag /*父工单是否有交付件（y/n）*/,
wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/,
wp.revenue_trigger_status /*触发状态(0：未触发过 1：已触发 2：已触发，pc校验触发失败 3：pc触发成功)*/,
wp.billing_status /*开票状态(空值：未触发过 1：已开票)*/,
wp.frozen_flag /*冻结标识（y/n）*/,
wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/,
wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p：部分完成，f：全部完成*/,
wp.tool_flag /*是否挂工具工单回写（y/n）*/,
wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/,
wp.mos_data_source /*站点签完工验状数据来源*/,
wo.template_id /*模板id，例如活动流节点id*/,
tfn.task_flow_id /*任务流id*/,
tfn.task_flow_node_id /*活动流节点id*/,
tfn.revenue_flag /*收入里程碑标识（y/n）*/,
tfn.on_site /*是否现场*/,
nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/,
tfn.subcon
/*是否分包*/
/*产品域*/,case
when wo.enable_flag = 'Y'
and wp.enable_flag = 'Y'
and wo.wo_lifecycle_status = 0
and nvl(du.enable_flag, 'Y') = 'Y' then 'Y'
else 'N'
end as enable_flag /*有效标识，y为有效n为失效*/,
'N' as del_flag /*删除标识 y为已删除*/,
3 as data_center_id /*数据中心id*/,
tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/,
tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/,
tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/,
tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/,
tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/,
wo.tenant_code /*租户编码 add by jwx528041 20200408*/,
tfn.activity_id /*活动流水号 add by jwx528041 20200408*/,
tfn.lead_time /*持续时间 add by jwx528041 20200408*/,
wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/,
wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/,
wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/,
wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/,
nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/,
tfn.on_line_site /*是否上站 add by cwx613468 20200711*/,
u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/,
tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/,
tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/,
tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/,
wp.source_code /*标识actual时间的修改来源，值为mobile标识从手机端回写 add by jwx528041 20200819*/,
wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/,
wp.dispatch_time /*调度时间 add by jwx528041 20200819*/,
wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/,
wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/,
wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/,
wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/,
wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/,
wp.revenue_trigger_failed_msg
/*收入触发失败原因 add by jwx528041 20200819*/,
ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/
--,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/
,
wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/,
dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/,
l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/,
u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/,
rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/,
t.billing_sla /*sla*/,
t.billing_milestone /*开票里程碑*/,
tf.required_tools,
wp.active,
gp.plan_code,
gp.plan_name,
gp.template_plan_id
from
sdisd.ogg_wo_work_order_2_3220 wo
inner join sdisd.ogg_wo_progress_2_3220 wp on wo.work_order_id = wp.work_order_id
left join sdisd.ogg_wo_task_flow_node_br_3220 tfn on wo.template_id = tfn.task_flow_node_id
and nvl(wo.wo_version, 0) = case
when nvl(wo.wo_version, 0) > 0 then tfn.version
else tfn.wo_version
end
and wo.project_number = tfn.project_number
left join sdisd.ogg_sds_activity_t_br_3220 ac on wo.activity_lib_id = ac.activity_id
left join sdisd.ogg_sds_task_flow_t_br_3220 tf on tfn.task_flow_id = tf.task_flow_id
left join sdisd.ogg_du_release_t_br_3220 du
/*enable_flag新增有效du的判断 lwx617215 20210116*/
on wo.du_id = du.du_id
left join sdisd.ogg_gcc_plan_2_3220 gp --dwx1189869
on wo.plan_id = gp.plan_id
and gp.tenant_code = 'RolloutPlan'
and gp.parent_plan_id = -1
and gp.enable_flag = 'Y'
left join (
select
r.du_id,
r.task_flow_id,
/*du与活动流有效标识*/
case
when r.enable_flag = 'Y'
and publish_flag = 'P' then 'Y'
else 'N'
end as du_tf_rel_enable,
row_number() over(
partition by r.du_id,
r.task_flow_id
order by
r.last_update_date desc
) as rn
from
sdisd.ogg_rp_du_tf_release_3_3220 r
) rel on wo.du_id = rel.du_id
and tfn.task_flow_id = rel.task_flow_id
and rel.rn = 1
left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id
left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id
left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id
left join sdisd.ogg_sds_activity_gap_t_br_3220 ag on wp.delay_reason_id = ag.activity_gap_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code
and l1.classify_code = 'SDS_TASK_OWNER_TYPE'
and l1.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code
and l2.classify_code = 'WO_STATUS_CODE'
and l2.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code
and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS'
and l3.language = 'en_US'
left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code
and l4.classify_code = 'SDS_TASK_ON_SITE'
and l4.language = 'en_US'
left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id
left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id
left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code
and l5.classify_code = 'PM_RESOURCE_TYPE'
and l5.language = 'zh_CN'
left join sdisd.ogg_sds_task_flow_node_br_3220 t on tfn.task_flow_node_id = t.task_flow_node_id
where
(
wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
)
union all
select
wo.work_order_id
/*工单id*/,
wo.work_order_code
/*工单编码*/,
wo.work_order_name
/*工单名称*/,
wo.work_order_level
/*工单层级(第一层级（未拆分工单/父工单）：1，第二层级(子工单)：10)*/,
decode(
wo.work_order_level,
1,
'第一层级（未拆分工单/父工单）',
10,
'第二层级(子工单)'
) as work_order_level_desc
/*工单层级描述*/,
substrb(wo.wo_description, 1, 1000) as wo_description
/*工单描述*/,
wo.wo_version
/*工单版本号*/,
wo.wo_lifecycle_status
/*生命周期标识：0：正常工单，-1: 已删除*/,
wo.business_id
/*工单来源业务id*/,
wo.business_type
/*工单来源业务类型（10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10）*/,
decode(
wo.business_type,
'10',
'活动流工单',
'20',
'手工派单',
'30',
'拆单工单',
'40',
'临时MOS工单',
'50',
'ihub工单',
'60',
'ipmo工单',
'70',
'WBS工单',
'80',
'NCS工单',
'90',
'HR工单',
'100',
'LS工单'
) as business_type_desc
/*工单来源业务类型描述*/,
wo.parent_activity_id
/*父节点活动id*/,
wo.activity_lib_id
/*活动库活动id*/,
wo.activity_type
/*作业类型，1wbs，2活动，3里程碑*/,
ac.activity_name
/*活动名称*/,
ac.std_ms_code as standard_ms_code
/*标准里程碑编码*/,
wo.plan_id
/*计划id*/,
wo.project_number as proj_num
/*项目编码*/,
wo.du_id
/*交付单元id*/,
wo.duration
/*工期*/,
wo.billing_flag
/*开票标识:y-开票*/,
wo.na_flag
/*na标识*/,
wo.inv_flag
/*inv标识*/,
wo.master_flag
/*拆分标示，n：未拆分 ; y:已拆分*/,
wo.created_by as created_by_id
/*创建人user id*/,
u1.lname as created_by
/*创建人*/,
wo.creation_date
/*创建时间*/,
wo.last_updated_by as last_updated_by_id
/*最后更新人user id*/,
u2.lname as last_updated_by
/*最后更新人*/,
wo.last_update_date
/*最后更新时间*/,
wp.wo_progress_id
/*活动进度id*/,
wp.expect_start_date
/*预期开始日期*/,
wp.expect_end_date
/*预期结束日期*/,
wp.plan_start_time
/*计划开始时间*/,
wp.plan_end_time
/*计划完成时间*/,
wp.actual_start_time
/*实际开始时间*/,
wp.actual_end_time
/*实际完成时间*/,
wp.close_time
/*活动关闭时间*/,
wp.completion_rate
/*完工比率（数值如 0.8666）*/,
to_char(substr(wp.remark, 1, 333)) as progress_description
/*进度备注信息*/,
wp.total_value
/*总值*/,
wp.accumulate_value
/*累计值*/,
wp.report_time
/*值反馈时间*/,
wp.total_plan_value
/*总计划值*/,
wp.ehs_risk
/*高危活动类型*/,
wp.delay_reason_id
/*延迟原因id*/,
substrb(ag.description, 1, 1000) as delay_reason_description
/*延迟原因描述*/,
wp.wo_status
/*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/,
l2.item_name as wo_status_desc
/*活动状态描述*/,(
case
when lengthb(wp.approve_status) = 0 then null
else wp.approve_status
end
) :: number as approve_status
/*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/,
l3.item_name as approve_status_desc
/*审批状态描述*/,
wp.par_workorder_doc_flag
/*父工单是否有交付件（y/n）*/,
wp.deliverables_complete
/*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/,
wp.revenue_trigger_status
/*触发状态(0：未触发过 1：已触发 2：已触发，pc校验触发失败 3：pc触发成功)*/,
wp.billing_status
/*开票状态(空值：未触发过 1：已开票)*/,
wp.frozen_flag
/*冻结标识（y/n）*/,
wp.mr_frozen_flag
/*mr是否冻结站点要货通过更新实施计划刷新字段*/,
wp.mr_status
/*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p：部分完成，f：全部完成*/,
wp.tool_flag
/*是否挂工具工单回写（y/n）*/,
wp.split_cp_flag
/*拆分施工计划标识 y已拆分 n未拆分*/,
wp.mos_data_source
/*站点签完工验状数据来源*/,
wo.template_id
/*模板id，例如活动流节点id*/,
tfn.task_flow_id
/*任务流id*/,
tfn.task_flow_node_id
/*活动流节点id*/,
tfn.revenue_flag
/*收入里程碑标识（y/n）*/,
tfn.on_site
/*是否现场*/,
nvl(l1.item_name, tfn.owner_type) as owner_type
/*责任方类型 客户/华为/分包商*/,
tfn.subcon
/*是否分包*/
/*产品域*/,case
when wo.enable_flag = 'Y'
and wp.enable_flag = 'Y'
and wo.wo_lifecycle_status = 0
and nvl(du.enable_flag, 'Y') = 'Y' then 'Y'
else 'N'
end as enable_flag
/*有效标识，y为有效n为失效*/,
'N' as del_flag
/*删除标识 y为已删除*/,
4 as data_center_id
/*数据中心id*/,
tf.task_flow_code
/*活动流编码 add by jwx528041 20200408*/,
tfn.task_flow_node_code
/*任务流节点编码 add by jwx528041 20200408*/,
tfn.task_flow_node_name
/*任务流节点名称 add by jwx528041 20200408*/,
tfn.task_flow_node_type
/*任务流节点类型 add by jwx528041 20200408*/,
tfn.enable_flag as flow_enable_flag
/*活动流有效标识 add by jwx528041 20200408*/,
wo.tenant_code
/*租户编码 add by jwx528041 20200408*/,
tfn.activity_id
/*活动流水号 add by jwx528041 20200408*/,
tfn.lead_time
/*持续时间 add by jwx528041 20200408*/,
wo.resource_id as wo_actual_owner_id
/*工单实际责任人id update by swx949890 202207*/,
wo.resource_name as wo_actual_owner
/*工单实际责任人 update by swx949890 202207*/,
wo.contractor_id as wo_actual_owner_contr_id
/*工单实际责任人分包商id update by swx949890 202207*/,
wo.contractor_name as wo_actual_owner_contr_name
/*工单实际责任人分包商名称 update by swx949890 202207*/,
nvl(l4.item_name, tfn.delivery_model) as delivery_model
/*工单交付模式 add by cwx613468 20200711*/,
tfn.on_line_site
/*是否上站 add by cwx613468 20200711*/,
u3.lname as dispatcher_user_name
/*调度人 add by cwx613468 20200711*/,
tfn.approve_level_qty
/*审批总层级 add by jwx528041 20200819*/,
tf.task_flow_name
/*活动流名称 add by jwx528041 20200819*/,
tf.task_flow_type
/*活动流类型 add by jwx528041 20200819*/,
wp.source_code
/*标识actual时间的修改来源，值为mobile标识从手机端回写 add by jwx528041 20200819*/,
wp.plan_updated_by
/*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.plan_update_time
/*计划时间更新时间 add by jwx528041 20200819*/,
wp.dispatch_time
/*调度时间 add by jwx528041 20200819*/,
wp.first_actual_update_time
/*第一次实际开始时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_end_time
/*第一次实际结束时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_updated_by
/*第一次实际时间填入人user id add by jwx528041 20200819*/,
wp.actual_start_update_time
/*实际开始时间更新日期 add by jwx528041 20200819*/,
wp.actual_start_updated_by
/*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.actual_time_source
/*实际完成时间更新来源 add by jwx528041 20200819*/,
wp.actual_end_update_time
/*实际完成时间更新日期 add by jwx528041 20200819*/,
wp.actual_end_updated_by
/*实际完成时间更新人user id add by jwx528041 20200819*/,
wp.revenue_trigger_failed_msg
/*收入触发失败原因 add by jwx528041 20200819*/,
ag.souce_type as delay_reason_souce_type
/*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/
--,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/
,
wo.tree_type as wo_owner_tree_type
/*工单责任人项目树节点类型tree_type update by swx949890 202207*/,
dr.resouce_type as wo_owner_resouce_type
/*工单责任人资源类型 add by jwx528041 20200819*/,
l5.item_name as wo_owner_resouce_type_desc
/*工单责任人资源类型 add by jwx528041 20200819*/,
u4.w3_account as wo_owner_w3_account
/*工单责任人w3账号 add by jwx528041 20200819*/,
rel.du_tf_rel_enable
/*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/,
t.billing_sla
/*sla*/,
t.billing_milestone
/*开票里程碑*/,
tf.required_tools,
wp.active,
gp.plan_code,
gp.plan_name,
gp.template_plan_id
from
sdisd.ogg_wo_work_order17_3220 wo
inner join sdisd.ogg_wo_progress17_3220 wp on wo.work_order_id = wp.work_order_id
left join sdisd.ogg_wo_task_flow_node_za_3220 tfn on wo.template_id = tfn.task_flow_node_id
and nvl(wo.wo_version, 0) = case
when nvl(wo.wo_version, 0) > 0 then tfn.version
else tfn.wo_version
end
and wo.project_number = tfn.project_number
left join sdisd.ogg_sds_activity_t_za_3220 ac on wo.activity_lib_id = ac.activity_id
left join sdisd.ogg_sds_task_flow_t_za_3220 tf on tfn.task_flow_id = tf.task_flow_id
left join sdisd.ogg_du_release_t_za_3220 du
/*enable_flag新增有效du的判断 lwx617215 20210116*/
on wo.du_id = du.du_id
left join sdisd.ogg_gcc_plan17_3220 gp --dwx1189869
on wo.plan_id = gp.plan_id
and gp.tenant_code = 'RolloutPlan'
and gp.parent_plan_id = -1
and gp.enable_flag = 'Y'
left join (
select
r.du_id,
r.task_flow_id,
/*du与活动流有效标识*/
case
when r.enable_flag = 'Y'
and publish_flag = 'P' then 'Y'
else 'N'
end as du_tf_rel_enable,
row_number() over(
partition by r.du_id,
r.task_flow_id
order by
r.last_update_date desc
) as rn
from
sdisd.ogg_rp_du_tf_release18_3220 r
) rel on wo.du_id = rel.du_id
and tfn.task_flow_id = rel.task_flow_id
and rel.rn = 1
left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id
left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id
left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id
left join sdisd.ogg_sds_activity_gap_t_za_3220 ag on wp.delay_reason_id = ag.activity_gap_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code
and l1.classify_code = 'SDS_TASK_OWNER_TYPE'
and l1.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code
and l2.classify_code = 'WO_STATUS_CODE'
and l2.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code
and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS'
and l3.language = 'en_US'
left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code
and l4.classify_code = 'SDS_TASK_ON_SITE'
and l4.language = 'en_US'
left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id
left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id
left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code
and l5.classify_code = 'PM_RESOURCE_TYPE'
and l5.language = 'zh_CN'
left join sdisd.ogg_sds_task_flow_node_za_3220 t on tfn.task_flow_node_id = t.task_flow_node_id
where
(
wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
)) as t limit 10
3、【性能分析】
优化前SQL语句执行时间达到3600s，超时自动报错，如下图所示：
可以看出（具体verbose执行计划如附件1所示），verbose执行计划中存在过多的NestLoop算子，一般情况下，该算子影响SQL语句执行性能，应该尽可能避免使用。通常可以利用语句
set [global] (enable_index_nestloop off)
来避免执行器走NestLoop算子。但有些场景下，该语句无法保证不使用NestLoop算子。因此，可以从另一方面入手解决这一问题，优化器因为对表估算不准确，故给出NestLoop算子的方案，可以利用tablescan这一hint对表进行全表扫描，以保证执行器走HashJoin算子而非NestLoop算子，从而提高语句执行性能。
注意：在使用tablescan这个hint时要保证NestLoop算子涉及到的表都要加上
优化后的SQL语句如下所示：
select * from ( select/*+tablescan(wp) tablescan(wo) tablescan(du) tablescan(ac) tablescan(u3) tablescan(u1) tablescan(u2) tablescan(tn) tablescan(dr) tablescan(u4)
tablescan(t)*/
wo.work_order_id /*工单id*/,
wo.work_order_code /*工单编码*/,
wo.work_order_name /*工单名称*/,
wo.work_order_level /*工单层级(第一层级（未拆分工单/父工单）：1，第二层级(子工单)：10)*/,
decode(wo.work_order_level,1, '第一层级（未拆分工单/父工单）', 10,'第二层级(子工单)') as work_order_level_desc /*工单层级描述*/,
substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/,
wo.wo_version /*工单版本号*/,
wo.wo_lifecycle_status /*生命周期标识：0：正常工单，-1: 已删除*/,
wo.business_id /*工单来源业务id*/,
wo.business_type /*工单来源业务类型（10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10）*/,
decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单',
'70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/,
wo.parent_activity_id /*父节点活动id*/,
wo.activity_lib_id /*活动库活动id*/,
wo.activity_type /*作业类型，1wbs，2活动，3里程碑*/,
ac.activity_name /*活动名称*/,
ac.std_ms_code as standard_ms_code /*标准里程碑编码*/,
wo.plan_id /*计划id*/,
wo.project_number as proj_num /*项目编码*/,
wo.du_id /*交付单元id*/,
wo.duration /*工期*/,
wo.billing_flag /*开票标识:y-开票*/,
wo.na_flag /*na标识*/,
wo.inv_flag /*inv标识*/,
wo.master_flag /*拆分标示，n：未拆分 ; y:已拆分*/,
wo.created_by as created_by_id /*创建人user id*/,
u1.lname as created_by /*创建人*/,
wo.creation_date /*创建时间*/,
wo.last_updated_by as last_updated_by_id /*最后更新人user id*/,
u2.lname as last_updated_by /*最后更新人*/,
wo.last_update_date /*最后更新时间*/,
wp.wo_progress_id /*活动进度id*/,
wp.expect_start_date /*预期开始日期*/,
wp.expect_end_date /*预期结束日期*/,
wp.plan_start_time /*计划开始时间*/,
wp.plan_end_time /*计划完成时间*/,
wp.actual_start_time /*实际开始时间*/,
wp.actual_end_time /*实际完成时间*/,
wp.close_time /*活动关闭时间*/,
wp.completion_rate /*完工比率（数值如 0.8666）*/,
to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/,
wp.total_value /*总值*/,
wp.accumulate_value /*累计值*/,
wp.report_time /*值反馈时间*/,
wp.total_plan_value /*总计划值*/,
wp.ehs_risk /*高危活动类型*/,
wp.delay_reason_id /*延迟原因id*/,
substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/,
wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/,
l2.item_name as wo_status_desc /*活动状态描述*/,(
case
when lengthb(wp.approve_status) = 0 then null
else wp.approve_status
end
) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/,
l3.item_name as approve_status_desc /*审批状态描述*/,
wp.par_workorder_doc_flag /*父工单是否有交付件（y/n）*/,
wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/,
wp.revenue_trigger_status /*触发状态(0：未触发过 1：已触发 2：已触发，pc校验触发失败 3：pc触发成功)*/,
wp.billing_status /*开票状态(空值：未触发过 1：已开票)*/,
wp.frozen_flag /*冻结标识（y/n）*/,
wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/,
wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p：部分完成，f：全部完成*/,
wp.tool_flag /*是否挂工具工单回写（y/n）*/,
wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/,
wp.mos_data_source /*站点签完工验状数据来源*/,
wo.template_id /*模板id，例如活动流节点id*/,
tfn.task_flow_id /*任务流id*/,
tfn.task_flow_node_id /*活动流节点id*/,
tfn.revenue_flag /*收入里程碑标识（y/n）*/,
tfn.on_site /*是否现场*/,
nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/,
tfn.subcon
/*是否分包*/
/*产品域*/,case
when wo.enable_flag = 'Y'
and wp.enable_flag = 'Y'
and wo.wo_lifecycle_status = 0
and nvl(du.enable_flag, 'Y') = 'Y' then 'Y'
else 'N'
end as enable_flag /*有效标识，y为有效n为失效*/,
'N' as del_flag /*删除标识 y为已删除*/,
3 as data_center_id /*数据中心id*/,
tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/,
tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/,
tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/,
tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/,
tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/,
wo.tenant_code /*租户编码 add by jwx528041 20200408*/,
tfn.activity_id /*活动流水号 add by jwx528041 20200408*/,
tfn.lead_time /*持续时间 add by jwx528041 20200408*/,
wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/,
wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/,
wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/,
wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/,
nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/,
tfn.on_line_site /*是否上站 add by cwx613468 20200711*/,
u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/,
tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/,
tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/,
tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/,
wp.source_code /*标识actual时间的修改来源，值为mobile标识从手机端回写 add by jwx528041 20200819*/,
wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/,
wp.dispatch_time /*调度时间 add by jwx528041 20200819*/,
wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/,
wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/,
wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/,
wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/,
wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/,
wp.revenue_trigger_failed_msg
/*收入触发失败原因 add by jwx528041 20200819*/,
ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/
--,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/
,
wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/,
dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/,
l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/,
u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/,
rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/,
t.billing_sla /*sla*/,
t.billing_milestone /*开票里程碑*/,
tf.required_tools,
wp.active,
gp.plan_code,
gp.plan_name,
gp.template_plan_id
from
sdisd.ogg_wo_work_order_2_3220 wo
inner join sdisd.ogg_wo_progress_2_3220 wp on wo.work_order_id = wp.work_order_id
left join sdisd.ogg_wo_task_flow_node_br_3220 tfn on wo.template_id = tfn.task_flow_node_id
and nvl(wo.wo_version, 0) = case
when nvl(wo.wo_version, 0) > 0 then tfn.version
else tfn.wo_version
end
and wo.project_number = tfn.project_number
left join sdisd.ogg_sds_activity_t_br_3220 ac on wo.activity_lib_id = ac.activity_id
left join sdisd.ogg_sds_task_flow_t_br_3220 tf on tfn.task_flow_id = tf.task_flow_id
left join sdisd.ogg_du_release_t_br_3220 du
/*enable_flag新增有效du的判断 lwx617215 20210116*/
on wo.du_id = du.du_id
left join sdisd.ogg_gcc_plan_2_3220 gp --dwx1189869
on wo.plan_id = gp.plan_id
and gp.tenant_code = 'RolloutPlan'
and gp.parent_plan_id = -1
and gp.enable_flag = 'Y'
left join (
select
r.du_id,
r.task_flow_id,
/*du与活动流有效标识*/
case
when r.enable_flag = 'Y'
and publish_flag = 'P' then 'Y'
else 'N'
end as du_tf_rel_enable,
row_number() over(
partition by r.du_id,
r.task_flow_id
order by
r.last_update_date desc
) as rn
from
sdisd.ogg_rp_du_tf_release_3_3220 r
) rel on wo.du_id = rel.du_id
and tfn.task_flow_id = rel.task_flow_id
and rel.rn = 1
left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id
left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id
left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id
left join sdisd.ogg_sds_activity_gap_t_br_3220 ag on wp.delay_reason_id = ag.activity_gap_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code
and l1.classify_code = 'SDS_TASK_OWNER_TYPE'
and l1.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code
and l2.classify_code = 'WO_STATUS_CODE'
and l2.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code
and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS'
and l3.language = 'en_US'
left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code
and l4.classify_code = 'SDS_TASK_ON_SITE'
and l4.language = 'en_US'
left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id
left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id
left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code
and l5.classify_code = 'PM_RESOURCE_TYPE'
and l5.language = 'zh_CN'
left join sdisd.ogg_sds_task_flow_node_br_3220 t on tfn.task_flow_node_id = t.task_flow_node_id
where
(
wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
)
union all
select
wo.work_order_id
/*工单id*/,
wo.work_order_code
/*工单编码*/,
wo.work_order_name
/*工单名称*/,
wo.work_order_level
/*工单层级(第一层级（未拆分工单/父工单）：1，第二层级(子工单)：10)*/,
decode(
wo.work_order_level,
1,
'第一层级（未拆分工单/父工单）',
10,
'第二层级(子工单)'
) as work_order_level_desc
/*工单层级描述*/,
substrb(wo.wo_description, 1, 1000) as wo_description
/*工单描述*/,
wo.wo_version
/*工单版本号*/,
wo.wo_lifecycle_status
/*生命周期标识：0：正常工单，-1: 已删除*/,
wo.business_id
/*工单来源业务id*/,
wo.business_type
/*工单来源业务类型（10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10）*/,
decode(
wo.business_type,
'10',
'活动流工单',
'20',
'手工派单',
'30',
'拆单工单',
'40',
'临时MOS工单',
'50',
'ihub工单',
'60',
'ipmo工单',
'70',
'WBS工单',
'80',
'NCS工单',
'90',
'HR工单',
'100',
'LS工单'
) as business_type_desc
/*工单来源业务类型描述*/,
wo.parent_activity_id
/*父节点活动id*/,
wo.activity_lib_id
/*活动库活动id*/,
wo.activity_type
/*作业类型，1wbs，2活动，3里程碑*/,
ac.activity_name
/*活动名称*/,
ac.std_ms_code as standard_ms_code
/*标准里程碑编码*/,
wo.plan_id
/*计划id*/,
wo.project_number as proj_num
/*项目编码*/,
wo.du_id
/*交付单元id*/,
wo.duration
/*工期*/,
wo.billing_flag
/*开票标识:y-开票*/,
wo.na_flag
/*na标识*/,
wo.inv_flag
/*inv标识*/,
wo.master_flag
/*拆分标示，n：未拆分 ; y:已拆分*/,
wo.created_by as created_by_id
/*创建人user id*/,
u1.lname as created_by
/*创建人*/,
wo.creation_date
/*创建时间*/,
wo.last_updated_by as last_updated_by_id
/*最后更新人user id*/,
u2.lname as last_updated_by
/*最后更新人*/,
wo.last_update_date
/*最后更新时间*/,
wp.wo_progress_id
/*活动进度id*/,
wp.expect_start_date
/*预期开始日期*/,
wp.expect_end_date
/*预期结束日期*/,
wp.plan_start_time
/*计划开始时间*/,
wp.plan_end_time
/*计划完成时间*/,
wp.actual_start_time
/*实际开始时间*/,
wp.actual_end_time
/*实际完成时间*/,
wp.close_time
/*活动关闭时间*/,
wp.completion_rate
/*完工比率（数值如 0.8666）*/,
to_char(substr(wp.remark, 1, 333)) as progress_description
/*进度备注信息*/,
wp.total_value
/*总值*/,
wp.accumulate_value
/*累计值*/,
wp.report_time
/*值反馈时间*/,
wp.total_plan_value
/*总计划值*/,
wp.ehs_risk
/*高危活动类型*/,
wp.delay_reason_id
/*延迟原因id*/,
substrb(ag.description, 1, 1000) as delay_reason_description
/*延迟原因描述*/,
wp.wo_status
/*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/,
l2.item_name as wo_status_desc
/*活动状态描述*/,(
case
when lengthb(wp.approve_status) = 0 then null
else wp.approve_status
end
) :: number as approve_status
/*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/,
l3.item_name as approve_status_desc
/*审批状态描述*/,
wp.par_workorder_doc_flag
/*父工单是否有交付件（y/n）*/,
wp.deliverables_complete
/*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/,
wp.revenue_trigger_status
/*触发状态(0：未触发过 1：已触发 2：已触发，pc校验触发失败 3：pc触发成功)*/,
wp.billing_status
/*开票状态(空值：未触发过 1：已开票)*/,
wp.frozen_flag
/*冻结标识（y/n）*/,
wp.mr_frozen_flag
/*mr是否冻结站点要货通过更新实施计划刷新字段*/,
wp.mr_status
/*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p：部分完成，f：全部完成*/,
wp.tool_flag
/*是否挂工具工单回写（y/n）*/,
wp.split_cp_flag
/*拆分施工计划标识 y已拆分 n未拆分*/,
wp.mos_data_source
/*站点签完工验状数据来源*/,
wo.template_id
/*模板id，例如活动流节点id*/,
tfn.task_flow_id
/*任务流id*/,
tfn.task_flow_node_id
/*活动流节点id*/,
tfn.revenue_flag
/*收入里程碑标识（y/n）*/,
tfn.on_site
/*是否现场*/,
nvl(l1.item_name, tfn.owner_type) as owner_type
/*责任方类型 客户/华为/分包商*/,
tfn.subcon
/*是否分包*/
/*产品域*/,case
when wo.enable_flag = 'Y'
and wp.enable_flag = 'Y'
and wo.wo_lifecycle_status = 0
and nvl(du.enable_flag, 'Y') = 'Y' then 'Y'
else 'N'
end as enable_flag
/*有效标识，y为有效n为失效*/,
'N' as del_flag
/*删除标识 y为已删除*/,
4 as data_center_id
/*数据中心id*/,
tf.task_flow_code
/*活动流编码 add by jwx528041 20200408*/,
tfn.task_flow_node_code
/*任务流节点编码 add by jwx528041 20200408*/,
tfn.task_flow_node_name
/*任务流节点名称 add by jwx528041 20200408*/,
tfn.task_flow_node_type
/*任务流节点类型 add by jwx528041 20200408*/,
tfn.enable_flag as flow_enable_flag
/*活动流有效标识 add by jwx528041 20200408*/,
wo.tenant_code
/*租户编码 add by jwx528041 20200408*/,
tfn.activity_id
/*活动流水号 add by jwx528041 20200408*/,
tfn.lead_time
/*持续时间 add by jwx528041 20200408*/,
wo.resource_id as wo_actual_owner_id
/*工单实际责任人id update by swx949890 202207*/,
wo.resource_name as wo_actual_owner
/*工单实际责任人 update by swx949890 202207*/,
wo.contractor_id as wo_actual_owner_contr_id
/*工单实际责任人分包商id update by swx949890 202207*/,
wo.contractor_name as wo_actual_owner_contr_name
/*工单实际责任人分包商名称 update by swx949890 202207*/,
nvl(l4.item_name, tfn.delivery_model) as delivery_model
/*工单交付模式 add by cwx613468 20200711*/,
tfn.on_line_site
/*是否上站 add by cwx613468 20200711*/,
u3.lname as dispatcher_user_name
/*调度人 add by cwx613468 20200711*/,
tfn.approve_level_qty
/*审批总层级 add by jwx528041 20200819*/,
tf.task_flow_name
/*活动流名称 add by jwx528041 20200819*/,
tf.task_flow_type
/*活动流类型 add by jwx528041 20200819*/,
wp.source_code
/*标识actual时间的修改来源，值为mobile标识从手机端回写 add by jwx528041 20200819*/,
wp.plan_updated_by
/*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.plan_update_time
/*计划时间更新时间 add by jwx528041 20200819*/,
wp.dispatch_time
/*调度时间 add by jwx528041 20200819*/,
wp.first_actual_update_time
/*第一次实际开始时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_end_time
/*第一次实际结束时间填入时间 add by jwx528041 20200819*/,
wp.first_actual_updated_by
/*第一次实际时间填入人user id add by jwx528041 20200819*/,
wp.actual_start_update_time
/*实际开始时间更新日期 add by jwx528041 20200819*/,
wp.actual_start_updated_by
/*实际开始时间更新人user id add by jwx528041 20200819*/,
wp.actual_time_source
/*实际完成时间更新来源 add by jwx528041 20200819*/,
wp.actual_end_update_time
/*实际完成时间更新日期 add by jwx528041 20200819*/,
wp.actual_end_updated_by
/*实际完成时间更新人user id add by jwx528041 20200819*/,
wp.revenue_trigger_failed_msg
/*收入触发失败原因 add by jwx528041 20200819*/,
ag.souce_type as delay_reason_souce_type
/*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/
--,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/
,
wo.tree_type as wo_owner_tree_type
/*工单责任人项目树节点类型tree_type update by swx949890 202207*/,
dr.resouce_type as wo_owner_resouce_type
/*工单责任人资源类型 add by jwx528041 20200819*/,
l5.item_name as wo_owner_resouce_type_desc
/*工单责任人资源类型 add by jwx528041 20200819*/,
u4.w3_account as wo_owner_w3_account
/*工单责任人w3账号 add by jwx528041 20200819*/,
rel.du_tf_rel_enable
/*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/,
t.billing_sla
/*sla*/,
t.billing_milestone
/*开票里程碑*/,
tf.required_tools,
wp.active,
gp.plan_code,
gp.plan_name,
gp.template_plan_id
from
sdisd.ogg_wo_work_order17_3220 wo
inner join sdisd.ogg_wo_progress17_3220 wp on wo.work_order_id = wp.work_order_id
left join sdisd.ogg_wo_task_flow_node_za_3220 tfn on wo.template_id = tfn.task_flow_node_id
and nvl(wo.wo_version, 0) = case
when nvl(wo.wo_version, 0) > 0 then tfn.version
else tfn.wo_version
end
and wo.project_number = tfn.project_number
left join sdisd.ogg_sds_activity_t_za_3220 ac on wo.activity_lib_id = ac.activity_id
left join sdisd.ogg_sds_task_flow_t_za_3220 tf on tfn.task_flow_id = tf.task_flow_id
left join sdisd.ogg_du_release_t_za_3220 du
/*enable_flag新增有效du的判断 lwx617215 20210116*/
on wo.du_id = du.du_id
left join sdisd.ogg_gcc_plan17_3220 gp --dwx1189869
on wo.plan_id = gp.plan_id
and gp.tenant_code = 'RolloutPlan'
and gp.parent_plan_id = -1
and gp.enable_flag = 'Y'
left join (
select
r.du_id,
r.task_flow_id,
/*du与活动流有效标识*/
case
when r.enable_flag = 'Y'
and publish_flag = 'P' then 'Y'
else 'N'
end as du_tf_rel_enable,
row_number() over(
partition by r.du_id,
r.task_flow_id
order by
r.last_update_date desc
) as rn
from
sdisd.ogg_rp_du_tf_release18_3220 r
) rel on wo.du_id = rel.du_id
and tfn.task_flow_id = rel.task_flow_id
and rel.rn = 1
left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id
left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id
left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id
left join sdisd.ogg_sds_activity_gap_t_za_3220 ag on wp.delay_reason_id = ag.activity_gap_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code
and l1.classify_code = 'SDS_TASK_OWNER_TYPE'
and l1.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code
and l2.classify_code = 'WO_STATUS_CODE'
and l2.language = 'en_US'
left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code
and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS'
and l3.language = 'en_US'
left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code
and l4.classify_code = 'SDS_TASK_ON_SITE'
and l4.language = 'en_US'
left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id
left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id
left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id
left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code
and l5.classify_code = 'PM_RESOURCE_TYPE'
and l5.language = 'zh_CN'
left join sdisd.ogg_sds_task_flow_node_za_3220 t on tfn.task_flow_node_id = t.task_flow_node_id
where
(
wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60
)) as t limit 10
如下图所示，该语句执行时间降为27s+，提升了语句的执行性能。
具体的performance执行计划如附件2所示。
- 附件：tablescan-performance.txt
- 附件：tablescan-verbose.txt
点击关注，第一时间了解华为云新鲜技术~
