pHP可以做论坛网站吗,网站用哪种语言,苏州保洁公司哪家最好,深圳燃气公司电话95511[13/JUL/2025, Yusuf Leo, Oracle SQL Performance Tuning Series]我们经常会遇到从同一表中按不同维度取出不同区间的数据#xff0c;再以相同的属性将这些数据分别汇总到一起的需求。这类需求往往迫使我们对同一个表反复去扫描#xff0c;当原始数据量太大的时候#xff0…[13/JUL/2025, Yusuf Leo, Oracle SQL Performance Tuning Series]我们经常会遇到从同一表中按不同维度取出不同区间的数据再以相同的属性将这些数据分别汇总到一起的需求。这类需求往往迫使我们对同一个表反复去扫描当原始数据量太大的时候这就可能给我们带来程序性能上的困扰。行转列PIVOT语法或许会是较好的优化思路之一。PIVOT需要Oracle 11g及以上版本支持。下面我们来看看这个实例这是某企业EBS客制化开发的一个报表核心逻辑是从ASCP工作台按订单类型区分统计各物料的总需求、在库、在途、在购等数量。优化前1.1 主程序主游标
cursor c1 isselect aa.organization_id,aa.plan_id,aa.item_segments,aa.description,aa.uom_code,aa.minimum_order_quantity, --MOQaa.fixed_lot_multiplier, --SPQaa.full_lead_time, --Lead Timeget_order_qty(aa.plan_id, aa.item_segments, 现有量) pr_qty1,get_order_qty(aa.plan_id, aa.item_segments, 采购订单) pr_qty7,get_order_qty(aa.plan_id, aa.item_segments, 采购申请) pr_qty8from (select distinct mov.organization_id,mov.plan_id,mov.item_segments,mov.description,mov.uom_code,msi.inventory_item_id,msi.minimum_order_quantity, --MOQmsi.fixed_lot_multiplier, --SPQmsi.full_lead_timefrom MSC_ORDERS_V mov, MTL_SYSTEM_ITEMS_B MSIwhere 1 1and msi.organization_id mov.organization_idand msi.segment1 mov.item_segmentsand (trunc(mov.new_order_date) to_date(p_date_f, YYYY-MM-DD) orp_date_f is null)and (trunc(mov.new_order_date) to_date(p_date_e, YYYY-MM-DD) orp_date_e is null)and mov.item_segments like % || p_item_segments || %and mov.plan_id p_plan_idand mov.organization_id p_organizatino_id) aa;1.2 主程序次级游标
cursor c2(p_item_code VARCHAR2) isselect bb.new_order_date,bb.new_due_date,get_plan_qty(bb.plan_id,bb.item_segments,计划单,bb.new_order_date,bb.new_due_date) po_qty, --采购数量get_need_qty(bb.plan_id, bb.item_segments, bb.new_due_date) need_qty --总需求数量from (select distinct mov.organization_id,mov.plan_id,mov.item_segments,to_char(mov.new_order_date, YYYY-MM-DD) new_order_date, --建议采购日期to_char(mov.new_due_date, YYYY-MM-DD) new_due_date --建议到期日from MSC_ORDERS_V mov, MSC_ORDERS_V mov1where 1 1and mov1.item_segments mov.item_segmentsand mov1.new_due_date mov.new_due_dateand mov1.new_order_date mov.new_order_dateand mov1.order_type_text 计划单 --物料有计划单的输出没有计划单的排除and (trunc(mov.new_order_date) to_date(p_date_f, YYYY-MM-DD) or p_date_f is null)and (trunc(mov.new_order_date) to_date(p_date_e, YYYY-MM-DD) or p_date_e is null)and mov.plan_id p_plan_idand mov.item_segments p_item_codeand mov.organization_id p_organization_idorder by new_due_date) bb;1.3 游标调用的子函数
-- get_order_qty 核心逻辑
select round(nvl(sum(mov.quantity_rate), 0), 2)from MSC_ORDERS_V movwhere 1 1and mov.category_set_id 1001and mov.item_segments p_item_codeand mov.order_type_text p_order_typeand mov.plan_id p_plan_id;-- get_plan_qty 核心逻辑
select round(nvl(sum(mov.quantity_rate), 0), 2)from MSC_ORDERS_V movwhere 1 1and trunc(mov.new_order_date) to_date(p_order_date, YYYY-MM-DD)and trunc(mov.new_due_date) to_date(p_due_date, YYYY-MM-DD)and mov.category_set_id 1001and mov.item_segments p_item_codeand mov.order_type_text p_order_typeand mov.plan_id p_plan_id;-- get_need_qty 核心逻辑
select abs(round(nvl(sum(mov.quantity_rate), 0), 2))from MSC_ORDERS_V movwhere 1 1and mov.new_due_date (to_date(p_due_date, YYYY-MM-DD) 6)and mov.category_set_id 1001and mov.item_segments p_item_codeand mov.order_type_text in (非标准任务需求,工作单需求,计划单需求,销售订单 MDS,预测 MDS)and mov.plan_id p_plan_id;问题分析该程序的主要逻辑是主程序首先遍历主游标从Msc_Oraders_V中取出符合参数条件的物料再代入次级游标中进一步取出符合要求的明细数据以打印输出并且这其中的很多数量数据是通过调用子函数计算。我们在两个游标中都看到了很不友好的DISTINCT去重进一步分析作者使用粗暴去重的原意发现两层游标的设计也并非必要次级游标中的“物料有计划单的输出没有计划单的排除”这个筛选条件其实可以通过EXISTS手段并入主游标而在主游标中先去重再调用子函数求值的方式则应考虑通过分组聚合的方式尝试简化写法。除了程序结构设计的问题该程序的性能问题还存在于对视图Msc_Oraders_V的反复扫描这是一个带有UNION ALL拼接的大型视图而程序中所有的数据其实都是来自这个视图困扰作者的可能是并不能通过简单的分组聚合直接满足功能设计的需求因为各汇总数据不仅是order_type_text不同而是同时在其它字段上又有不同范围的限制即三个子函数的区别。优化思路大方向是1、两级游标整合成一级2、拆解子函数入主游标原次级游标能够决定代入来的主游标物料是否打印则应把这个限制条件直接作为物料的筛选条件虽然子函数都是在读取Msc_Orders_V但又略有不同不能通过GROUP BY直接改写考虑尝试PIVOT原始扫描范围放为最大各列统计时再分别限制其范围。优化后
with plan_qtys as(select mov1.organization_id,mov1.plan_id,mov1.item_segments,trunc(mov1.new_order_date) new_order_date, --建议采购日期trunc(mov1.new_due_date) new_due_date, --建议到期日sum(casewhen mov1.category_set_id 1001 thenmov1.quantity_rateelse0end) plan_qtyfrom MSC_ORDERS_V mov1where 1 1and mov1.new_due_date is not nulland mov1.new_order_date is not nulland mov1.order_type_text 计划单 --物料有计划单的输出没有计划单的排除and mov1.new_order_date nvl(to_date(p_date_f, YYYY-MM-DD), mov1.new_order_date)and mov1.new_order_date nvl(to_date(p_date_e, YYYY-MM-DD) .99999, mov1.new_order_date)and mov1.plan_id p_plan_idand mov1.item_segments like % || p_item_segments || %and mov1.organization_id p_organization_idgroup by mov1.organization_id,mov1.plan_id,mov1.item_segments,trunc(mov1.new_order_date),trunc(mov1.new_due_date)),
mov_data as(select organization_id,item_segments,description,uom_code,new_order_date,new_due_date,round(nvl(pr_qty1, 0), 2) pr_qty1,round(nvl(pr_qty7, 0), 2) pr_qty7,round(nvl(pr_qty8, 0), 2) pr_qty8,round(nvl(plan_qty, 0), 2) plan_qty,abs(round(nvl((need_qty_q2), 0), 2)) need_qtyfrom (select mov.organization_id,mov.item_segments,mov.description,mov.uom_code,casewhen order_type_text in (非标准任务需求,工作单需求,计划单需求,销售订单 MDS,预测 MDS) then需求elseorder_type_textend as order_type_text,mov.quantity_rate order_qty,casewhen mov.new_due_date pq.new_due_date 6 thenmov.quantity_rateelse0end order_qty2,pq.plan_qty,pq.new_order_date,pq.new_due_datefrom MSC_ORDERS_V mov, plan_qtys pqwhere mov.organization_id pq.organization_idand mov.plan_id pq.plan_idand mov.item_segments pq.item_segmentsand mov.category_set_id 1001)pivot(sum(order_qty), sum(order_qty2) as q2for order_type_text in(现有量 as pr_qty1,采购订单 as pr_qty7,采购申请 as pr_qty8,需求 as need_qty)))
select mov.organization_id,mov.item_segments,mov.description,mov.uom_code, --单位msi.inventory_item_id,msi.minimum_order_quantity, --MOQmsi.fixed_lot_multiplier, --SPQmsi.full_lead_time,mov.pr_qty1,mov.pr_qty7,mov.pr_qty8,mov.need_qty,mov.plan_qty,mov.new_order_date,mov.new_due_datefrom mov_data mov, MTL_SYSTEM_ITEMS_B MSIwhere 1 1and msi.organization_id mov.organization_idand msi.segment1 mov.item_segmentsorder by item_segments, new_due_date优化前请求第二次运行有缓存用时14h51m42s优化后请求同参数第二次运行有缓存用时54s优化比例1:991[END]
