最近在项目上，客户让我帮忙看一个查询很慢的视图，前提是在给我之前他们内部已进行优化了，当我看了他们的优化，其实就是简单地使用hint语句，而且并没有起到作用。
首先大概描述一下这个视图的情况，它是由三段sql union组成，总共大概220+行，里面有部分字段值是通过DBLINK获取的。详细如下：

CREATE OR REPLACE VIEW CUX_DWMS_GROUP_LOT_ITA_V AS
select t.batch_no,
       t.delivery_detail_id,
       t.order_type,
       t.virtual_ship_flag,
       t.ship_number,
       t.source_header_number,
       t.source_line_number,
       t.reference_number,
       t.party_name,
       t.ship_add,
       t.item_number,
       t.item_name,
       t.requested_quantity,
       t.picked_qty,
       t.requested_quantity_uom,
       t.pick_person,
       t.schedule_ship_date,
       t.ordered_date,
       t.organization_id,
       t.organization_code,
       t.organization_name,
       t.subinventory,
       t.ship_method_code,
       t.ship_method,
       t.released_status,
       t.line_status,
       t.remark,
       t.creation_date,
       t.printed_flag,
       t.box_count,
       t.pack_method,
       t.ship_desctiption,
       t.ddt_type
  from (SELECT /*+ leading (ooh) index(ooh ONT.OE_ORDER_HEADERS_N11) index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/
         row_number() over(partition by trh.request_number, wdd.source_header_number, wdd.source_line_number order by wdd.delivery_detail_id desc) rn,
         trh.request_number batch_no,
         wdd.delivery_detail_id,
         ott.attribute2 order_type,
         ooh.attribute9 virtual_ship_flag,
         (select csi.print_num
            from cux_ship_input_info_ita csi
           where csi.batch_num = trh.request_number
             and csi.source_header_number = wdd.source_header_number) ship_number,
         wdd.source_header_number,
         wdd.source_line_number,
         wdd.reference_number,
         ooh.attribute5 party_name,
         ool.attribute1 || '.' || ool.attribute4 ship_add,
         msib.segment1 item_number,
         msib.description item_name,
         wdd.requested_quantity,
         nvl(trl.quantity_delivered, 0) picked_qty,
         wdd.requested_quantity_uom,
         ooh.attribute6 pick_person,
         ool.schedule_ship_date,
         ooh.ordered_date,
         ood.organization_id,
         ood.organization_code,
         ood.organization_name,
         wdd.ORIGINAL_SUBINVENTORY subinventory,
         ool.attribute2 ship_method_code,
         ool.attribute3 ship_method,
         wdd.released_status,
         flv.meaning line_status,
         ooh.attribute2 remark,
         ooh.creation_date,
         decode(csi.print_flag, NULL, 'N', 'Y') printed_flag,
         csi.box_count, --件数
         csi.pack_method, --装货方式
         csi.ship_desctiption, --描述
         (select flv.lookup_code
            from fnd_lookup_values_vl                                   flv,
                 apps.cux_mid_order_header_temp_t@db_iwms.dahuatech.com cmo,
                 --apps.oe_order_headers_all@db_iwms.dahuatech.com        ooh1,
                 cux_wms_so_sync_all cws
           where cmo.freasonsforshipment = flv.MEANING
             and flv.lookup_type = 'CUX_PRINT_TEMPLATE_MAPPING'
             and cmo.fodernumber = cws.order_number
             and cws.mo_number = ooh.order_number
             and rownum<2
             /*group by flv.lookup_code*/) ddt_type
          FROM wsh_delivery_details         wdd,
               wsh_delivery_assignments     wda,
               oe_order_headers_all         ooh,
               mtl_txn_request_lines        trl,
               mtl_txn_request_headers      trh,
               ont.oe_transaction_types_all ott,
               oe_order_lines_all           ool,
               mtl_system_items_b           msib,
               org_organization_definitions ood,
               fnd_lookup_values            flv,
               cux_ship_input_info_ita      csi
         WHERE 1 = 1
           AND trh.header_id = trl.header_id
           AND trl.line_id = wdd.move_order_line_id
           AND wda.delivery_detail_id = wdd.delivery_detail_id
           AND ool.header_id = ooh.header_id
           AND ooh.order_type_id = ott.transaction_type_id
           AND wdd.source_line_id = ool.line_id
           AND wdd.source_code = 'OE'
           AND wdd.inventory_item_id = msib.inventory_item_id
           AND wdd.organization_id = msib.organization_id
           AND wdd.organization_id = ood.organization_id
           AND flv.lookup_type = 'PICK_STATUS'
           AND flv.lookup_code = wdd.released_status
           AND flv.enabled_flag = 'Y'
           AND (SYSDATE BETWEEN flv.start_date_active AND
               nvl(flv.end_date_active, SYSDATE + 2))
           AND flv.language = userenv('LANG')
           AND ooh.flow_status_code in ('BOOKED', 'CLOSED')
           AND ood.ORGANIZATION_CODE = '941'
           AND wdd.released_status in ('Y', 'C')
           AND csi.batch_num = trh.request_number
           AND csi.source_header_number = wdd.source_header_number
           ) t
 WHERE t.rn = 1
 UNION
 --样品领用
 select /*+index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/mtrh1.request_number batch_no,
        0 delivery_detail_id,
        'Move Order' order_type,
        '' virtual_ship_flag,
        '' ship_number,
        '' source_header_number,
        '' source_line_number,
        '' reference_number,
        decode(cic.Accnt_name,
               '海外内部测试',
               cic.bus_owner,
               cic.accnt_name) party_name,
        cic.addr ship_add,
        cic.part_num item_number,
        msi.description item_name,
        mtrl.quantity requested_quantity,
        mmt.transaction_quantity picked_qty,
        msi.primary_uom_code requested_quantity_uom,
        (select to_char(user_name)
           from fnd_user
          where user_id = mmt.created_by) pick_person,
        mtrh.creation_date schedule_ship_date,
        mtrh.creation_date ordered_date,
        mmt.organization_id organization_id,
        cic.out_inv_org organization_code,
        (select organization_name
           from org_organization_definitions
          where organization_code = cic.out_inv_org) organization_name,
        mmt.subinventory_code subinventory,
        '' ship_method_code,
        '' ship_method,
        '' released_status,
        '' line_status,
        '' remark,
        sysdate creation_date,
        csi.print_flag printed_flag,
        csi.box_count box_count,
        csi.pack_method pack_method,
        csi.ship_desctiption ship_desctiption,
        decode(cic.accnt_name, '海外内部测试', 'B9', 'B4') DDT_TYPE
   from apps.cux_inv_crm_move_orders@db_iwms.dahuatech.com cic,
        apps.mtl_txn_request_headers@db_iwms.dahuatech.com mtrh,
        apps.mtl_txn_request_lines@db_iwms.dahuatech.com   mtrl,
        mtl_material_transactions                          mmt,
        mtl_txn_request_headers                            mtrh1,
        mtl_txn_request_lines                              mtrl1,
        mtl_system_items_b                                 msi,
        cux_ship_input_info_ita      csi
  where cic.out_inv_org = '941'
    and cic.process_status = 'S'
    and cic.move_order_header_id = mtrh.header_id
    and cic.move_order_line_id = mtrl.line_id
    and msi.inventory_item_id = mmt.inventory_item_id
    and msi.organization_id = mmt.organization_id
    and mtrh1.header_id = mmt.transaction_source_id
    and mtrl1.header_id = mtrl1.header_id
    and mtrl1.line_id = mmt.trx_source_line_id
    and mmt.transaction_type_id = 160
    and mmt.organization_id = 270
    and mmt.transaction_quantity > 0
    and mtrl1.line_number = mtrl.line_number
    and mtrh1.request_number = 'W' || mtrh.request_number
    and mtrh1.request_number=csi.batch_num(+)
UNION
--转库，针对中部仓和米兰仓
select /*+index (csi CUX.CUX_SHIP_INPUT_INFO_ITA_N01)*/mtrh.request_number batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       'Dahua Technology S.r.l.' party_name,
       'Via Brughetti, 9/h - 20813 Bovisio Masciago (MB)' ship_add,
       msi.segment1 item_number,
       msi.description item_name,
       mtrl.quantity requested_quantity,
       sum(mmt.transaction_quantity) picked_qty,
       msi.primary_uom_code requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = mmt.created_by) pick_person,
       mtrh.creation_date schedule_ship_date,
       mtrh.creation_date ordered_date,
       mmt.organization_id organization_id,
       ood.organization_code organization_code,
       ood.organization_name organization_name,
       mmt.subinventory_code subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       'S1' DDT_TYPE
  from mtl_material_transactions    mmt,
       mtl_txn_request_headers      mtrh,
       mtl_txn_request_lines        mtrl,
       mtl_system_items_b           msi,
       org_organization_definitions ood,
       cux_ship_input_info_ita      csi
 where mtrh.header_id = mmt.transaction_source_id
   and mtrl.line_id = mmt.trx_source_line_id
   and mtrh.header_id = mtrh.header_id
   and mmt.organization_id = mtrh.organization_id
   and msi.inventory_item_id = mtrl.inventory_item_id
   and msi.organization_id = mmt.organization_id
   and ood.ORGANIZATION_ID = mmt.ORGANIZATION_ID
   and mmt.transaction_type_id = 115 --子库存转移
   and mmt.organization_id = 270
   and mmt.transaction_quantity > 0
   and ((mtrl.to_subinventory_code = '91.02.01' and
       mtrl.from_subinventory_code = '91.02.02') or
       (mtrl.to_subinventory_code = '91.02.02' and
       mtrl.from_subinventory_code = '91.02.01'))
   and mtrh.request_number = csi.batch_num
   and not exists (select 1
          from mtl_txn_request_lines mtrl1
         where mtrl1.line_id = mtrl.line_id
           and mtrl1.line_status = ('7')) --确保搬运单行没有打开状态
--and mtrh.request_number = 'W10194646'
 group by mtrh.request_number,
          msi.segment1,
          msi.description,
          mtrl.quantity,
          msi.primary_uom_code,
          mmt.created_by,
          mtrh.creation_date,
          mtrh.creation_date,
          mmt.organization_id,
          ood.organization_code,
          ood.organization_name,
          mmt.subinventory_code ,
          csi.print_flag,
          csi.box_count,
          csi.pack_method,
          csi.ship_desctiption;

其次，在plsql里查询了一下这个视图，200+分钟过去了没有执行出结果，终于等到跑完打出trace日志文件。这段SQL出来的结果只有6条数据，为什么耗时这么长呢？


最后，我来说说我的sql优化思路吧

• 一般Sql优化都得从根入手，进行SQL调整，大多数技术或顾问或客户在写SQL时都是只顾执行结果不顾其在时间/空间等性能，所以大多数SQL都是千疮百孔的经不起推敲，那么在进行SQL调整的前提是确保结果正确。SQL调整目的在于两点 ：1、利用Oracle中的数据集驱动，让数据量小的结果集为主，关联其他表或对象；2、让where子句条件走索引，尽量走结果集小的索引。调整过后的视图如下：

CREATE OR REPLACE VIEW CUX_DWMS_GROUP_LOT_ITA_V AS
select t.batch_no,
       t.delivery_detail_id,
       t.order_type,
       t.virtual_ship_flag,
       t.ship_number,
       t.source_header_number,
       t.source_line_number,
       t.reference_number,
       t.party_name,
       t.ship_add,
       t.item_number,
       t.item_name,
       t.requested_quantity,
       t.picked_qty,
       t.requested_quantity_uom,
       t.pick_person,
       t.schedule_ship_date,
       t.ordered_date,
       t.organization_id,
       t.organization_code,
       t.organization_name,
       t.subinventory,
       t.ship_method_code,
       t.ship_method,
       t.released_status,
       t.line_status,
       t.remark,
       t.creation_date,
       t.printed_flag,
       t.box_count,
       t.pack_method,
       t.ship_desctiption,
       t.ddt_type
  from (SELECT row_number() over(partition by trh.request_number, wdd.source_header_number, wdd.source_line_number order by wdd.delivery_detail_id desc) rn,
               trh.request_number batch_no,
               wdd.delivery_detail_id,
               (select ott.attribute2
                  from ont.oe_transaction_types_all ott
                 where ooh.order_type_id = ott.transaction_type_id) order_type,
               ooh.attribute9 virtual_ship_flag,
               (select csi.print_num
                  from cux_ship_input_info_ita csi
                 where csi.batch_num = trh.request_number
                   and csi.source_header_number = wdd.source_header_number) ship_number,
               wdd.source_header_number,
               wdd.source_line_number,
               wdd.reference_number,
               ooh.attribute5 party_name,
               ool.attribute1 || '.' || ool.attribute4 ship_add,
               msib.segment1 item_number,
               msib.description item_name,
               wdd.requested_quantity,
               nvl(trl.quantity_delivered, 0) picked_qty,
               wdd.requested_quantity_uom,
               ooh.attribute6 pick_person,
               ool.schedule_ship_date,
               ooh.ordered_date,
               ood.organization_id,
               ood.organization_code,
               ood.organization_name,
               wdd.original_subinventory subinventory,
               ool.attribute2 ship_method_code,
               ool.attribute3 ship_method,
               wdd.released_status,
               (select flv.meaning
                  from fnd_lookup_values_vl flv
                 where 1 = 1
                   AND flv.lookup_type = 'PICK_STATUS'
                   AND flv.lookup_code = wdd.released_status
                   AND flv.enabled_flag = 'Y'
                   AND (SYSDATE BETWEEN flv.start_date_active AND
                       nvl(flv.end_date_active, SYSDATE + 2))) line_status,
               ooh.attribute2 remark,
               ooh.creation_date,
               decode(csi.print_flag, NULL, 'N', 'Y') printed_flag,
               csi.box_count, --件数
               csi.pack_method, --装货方式
               csi.ship_desctiption, --描述
               (select flv.lookup_code
                  from fnd_lookup_values_vl                                   flv,
                       apps.cux_mid_order_header_temp_t@db_iwms.dahuatech.com cmo,
                       cux_wms_so_sync_all                                    cws -- 不走T2索引
                 where cmo.freasonsforshipment = flv.MEANING
                   and flv.lookup_type = 'CUX_PRINT_TEMPLATE_MAPPING'
                   and cmo.fodernumber = cws.order_number
                   and cws.mo_number = ooh.order_number
                   and rownum = 1) ddt_type
          FROM wsh_delivery_details         wdd,
               oe_order_headers_all         ooh,
               oe_order_lines_all           ool,
               mtl_txn_request_lines        trl,
               mtl_txn_request_headers      trh,
               mtl_system_items_b           msib,
               org_organization_definitions ood,
               cux_ship_input_info_ita      csi
         WHERE 1 = 1
           and wdd.source_header_id = ooh.header_id
           AND wdd.source_line_id = ool.line_id
           AND wdd.source_code = 'OE'
           AND wdd.released_status in ('Y', 'C')
           and wdd.inventory_item_id = ool.inventory_item_id
           AND wdd.move_order_line_id = trl.line_id
           and wdd.inventory_item_id = trl.inventory_item_id
           and wdd.organization_id = trl.organization_id
           AND wdd.inventory_item_id = msib.inventory_item_id
           AND wdd.organization_id = msib.organization_id
           AND wdd.organization_id = ood.organization_id
           AND trh.header_id = trl.header_id
           AND ooh.header_id = ool.header_id
           AND ooh.flow_status_code in ('BOOKED', 'CLOSED')
           AND ood.organization_id = 270
           AND csi.batch_num = trh.request_number
           AND csi.source_header_number = wdd.source_header_number) t
 WHERE t.rn = 1

UNION
--样品领用
select wms_temp.request_number batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       decode(ebs_temp.accnt_name,
              '海外内部测试',
              ebs_temp.bus_owner,
              ebs_temp.accnt_name) party_name,
       ebs_temp.addr ship_add,
       ebs_temp.part_num item_number,
       wms_temp.description item_name,
       ebs_temp.quantity requested_quantity,
       wms_temp.transaction_quantity picked_qty,
       wms_temp.primary_uom_code requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = wms_temp.created_by) pick_person,
       ebs_temp.creation_date schedule_ship_date,
       ebs_temp.creation_date ordered_date,
       wms_temp.organization_id organization_id,
       ebs_temp.out_inv_org organization_code,
       (select organization_name
          from org_organization_definitions
         where organization_code = ebs_temp.out_inv_org) organization_name,
       wms_temp.subinventory_code subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       decode(ebs_temp.accnt_name, '海外内部测试', 'B9', 'B4') ddt_type
  from (select cic.addr,
               cic.part_num,
               mtrl.quantity,
               mtrh.creation_date,
               mtrh.request_number,
               mtrl.line_number,
               cic.out_inv_org,
               cic.bus_owner,
               cic.accnt_name
          from apps.cux_inv_crm_move_orders@db_iwms.dahuatech.com cic,
               apps.mtl_txn_request_headers@db_iwms.dahuatech.com mtrh,
               apps.mtl_txn_request_lines@db_iwms.dahuatech.com   mtrl
         where cic.out_inv_org = '941'
           and cic.process_status = 'S'
           and cic.move_order_header_id = mtrh.header_id
           and cic.move_order_line_id = mtrl.line_id
           and mtrh.header_id = mtrl.line_id) ebs_temp,
       (select mtrh1.request_number,
               mtrl1.line_number,
               msi.description,
               mmt.transaction_quantity,
               msi.primary_uom_code,
               mmt.created_by,
               mmt.organization_id,
               mmt.subinventory_code
          from mtl_material_transactions mmt,
               mtl_txn_request_headers   mtrh1,
               mtl_txn_request_lines     mtrl1,
               mtl_system_items_b        msi
         where 1 = 1
           and msi.inventory_item_id = mmt.inventory_item_id
           and msi.organization_id = mmt.organization_id
           and mtrh1.header_id = mmt.transaction_source_id
           and mtrl1.header_id = mtrl1.header_id
           and mtrl1.line_id = mmt.trx_source_line_id
           and mmt.transaction_type_id = 160
           and mmt.organization_id = 270
           and mmt.transaction_quantity > 0) wms_temp,
       cux_ship_input_info_ita csi
 where 1 = 1
   and wms_temp.line_number = ebs_temp.line_number
   and wms_temp.request_number = 'W' || ebs_temp.request_number
   and wms_temp.request_number = csi.batch_num(+)

UNION
--转库，针对中部仓和米兰仓
select mmt_temp.batch_no,
       0 delivery_detail_id,
       'Move Order' order_type,
       '' virtual_ship_flag,
       '' ship_number,
       '' source_header_number,
       '' source_line_number,
       '' reference_number,
       'Dahua Technology S.r.l.' party_name,
       'Via Brughetti, 9/h - 20813 Bovisio Masciago (MB)' ship_add,
       mmt_temp.item_number,
       mmt_temp.item_name,
       mmt_temp.requested_quantity,
       mmt_temp.picked_qty,
       mmt_temp.requested_quantity_uom,
       (select to_char(user_name)
          from fnd_user
         where user_id = mmt_temp.created_by) pick_person,
       mmt_temp.creation_date schedule_ship_date,
       mmt_temp.creation_date ordered_date,
       mmt_temp.organization_id,
       ood.organization_code organization_code,
       ood.organization_name organization_name,
       mmt_temp.subinventory,
       '' ship_method_code,
       '' ship_method,
       '' released_status,
       '' line_status,
       '' remark,
       sysdate creation_date,
       csi.print_flag printed_flag,
       csi.box_count box_count,
       csi.pack_method pack_method,
       csi.ship_desctiption ship_desctiption,
       'S1' ddt_type
  from (select mtrh.request_number batch_no,
               msi.segment1 item_number,
               msi.description item_name,
               mtrl.quantity requested_quantity,
               sum(mmt.transaction_quantity) picked_qty,
               msi.primary_uom_code requested_quantity_uom,
               mmt.created_by,
               mtrh.creation_date,
               mmt.organization_id,
               mmt.subinventory_code subinventory
          from mtl_material_transactions mmt,
               mtl_txn_request_headers   mtrh,
               inv.mtl_txn_request_lines mtrl,
               mtl_system_items_b        msi
         where 1 = 1
           and mmt.transaction_source_id = mtrh.header_id
           and mmt.trx_source_line_id = mtrl.line_id
           and mmt.organization_id = mtrl.organization_id
           and mmt.inventory_item_id = msi.inventory_item_id
           and mmt.organization_id = msi.organization_id
           and mtrh.header_id = mtrl.header_id
           and mmt.transaction_type_id = 115 --子库存转移
           and mmt.transaction_quantity > 0
           and mmt.organization_id = 270
           and ((mtrl.to_subinventory_code = '91.02.01' and
               mtrl.from_subinventory_code = '91.02.02') or
               (mtrl.to_subinventory_code = '91.02.02' and
               mtrl.from_subinventory_code = '91.02.01'))
           and mtrl.line_status <> '7' --确保搬运单行没有打开状态
         group by mtrh.request_number,
                  msi.segment1,
                  msi.description,
                  mtrl.quantity,
                  msi.primary_uom_code,
                  mmt.created_by,
                  mtrh.creation_date,
                  mmt.organization_id,
                  mmt.subinventory_code) mmt_temp,
       org_organization_definitions ood,
       cux_ship_input_info_ita csi
 where 1 = 1
   and mmt_temp.batch_no = csi.batch_num
   and mmt_temp.organization_id = ood.organization_id;

调整过后，cost 从 383.4w 降到了 1.8w。

• sql调整后再次查询视图，其执行时间并不理想，故而得通过trace文件去分析了。
  
  这里是select结果上的子查询，其表的T2索引是建在order_number字段上的，而且此处还有dblink。首先强烈建议客户调整一下此处取值sql，最不济的办法是用hint指定索引。
  
  这处也需要调整，但看了其sql和表上索引字段【move_order_line_id】，也并无不妥，如果一定要调整，只有调整索引，建在move_order_line_id 和released_status上缩小结果集，但个人不是很建议，理由后续。

如上调整后，执行时间控制在了20-50秒内。