GaussDB(DWS)性能调优：实时场景下表行数估算不准确引起的的性能瓶颈问题案例

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所示。

我正在参加【有奖征文 第27期】绽放吧！GaussDB(DWS)云原生数仓！
https://bbs.huaweicloud.com/blogs/412962