Oracle SQL - 使用行轉列PIVOT減少表重復掃描（實例）

[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]

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/914513.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/914513.shtml
英文地址，請注明出處：http://en.pswp.cn/news/914513.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

HTTP 請求方法詳解：GET、POST、PUT、DELETE 等

HTTP 請求方法詳解：GET、POST、PUT、DELETE 等

在 HTTP 協議中&#xff0c;請求方法&#xff08;也稱為 HTTP 動詞&#xff09;定義了客戶端希望對指定資源執行的操作類型。這些方法是 HTTP 報文的核心組成部分&#xff0c;決定了請求的目的和行為。 主要 HTTP 請求方法 1. GET 用途&#xff1a;獲取資源 特點&#xff1a…
閱讀更多...
Android 代碼熱度統計（概述）

Android 代碼熱度統計（概述）

1. 前言 代碼熱度統計&#xff0c;在測試中一般也叫做代碼覆蓋率。一般得到代碼覆蓋率后就能了解整體樣本在線上的代碼使用情況&#xff0c;為無用代碼下線提供依據。 做了一下調研&#xff0c;在Android中一般比較常用的是&#xff1a;JaCoCO覆蓋率統計工具&#xff0c;它采…
閱讀更多...
RAG優化

RAG優化

RAG搭建本地AI知識庫&#xff0c;在使用過程中遇到的三大痛點&#xff0c;以及相應的進階方案。1. RAG知識庫的三大痛點-- 內容理解不足&#xff1a;AI難以全面理解導入資料的內容&#xff0c;比如在向量編碼時候&#xff0c;生硬的截斷等導致分析結果不理想。eg: 知識庫分割器…
閱讀更多...
Ubuntu 24.04 啟用 root 圖形登錄

Ubuntu 24.04 啟用 root 圖形登錄

關鍵詞&#xff1a;Ubuntu 24.04、root 登錄、GDM、SSH、nano、配置文件一、前言 Ubuntu 默認禁用 root 賬戶 的圖形與 SSH 登錄&#xff0c;這是為了安全。但在某些場景&#xff08;如測試、救援、自動化腳本&#xff09;你可能需要 直接用 root 登錄 GNOME 桌面。本文以 Ubun…
閱讀更多...
Jekyll + Chirpy + GitHub Pages 搭建博客

Jekyll + Chirpy + GitHub Pages 搭建博客

Chirpy 是適用于技術寫作的簡約、響應迅速且功能豐富的 Jekyll 主題&#xff0c;文檔地址&#xff1a;https://chirpy.cotes.page/ &#xff0c;Github 地址&#xff1a;jekyll-theme-chirpy 。 1.開始 打開 chirpy-starter 倉庫&#xff0c;點擊按鈕 Use this template -->…
閱讀更多...
學習 Flutter (一)

學習 Flutter (一)

學習 Flutter (一) 1. 引言 什么是 Flutter&#xff1f; Flutter 是 Google 開發的一套開源 UI 框架&#xff0c;主要用于構建高性能、高保真、跨平臺的應用程序。使用一套 Dart 編寫的代碼&#xff0c;開發者可以同時構建適用于&#xff1a; Android iOS Web Windows、mac…
閱讀更多...
Spring Boot 實現圖片防盜鏈：Referer 校驗與 Token 簽名校驗完整指南

Spring Boot 實現圖片防盜鏈：Referer 校驗與 Token 簽名校驗完整指南

Spring Boot 實現圖片防盜鏈教程&#xff08;Referer 校驗 Token 簽名校驗&#xff09;本文將詳細講解兩種防盜鏈實現方案&#xff0c;并提供完整代碼示例。方案一&#xff1a;Referer 校驗通過檢查 HTTP 請求頭中的 Referer 字段判斷來源是否合法。實現步驟創建 Referer 攔截…
閱讀更多...
從 JSON 到 Python 對象：一次通透的序列化與反序列化之旅

從 JSON 到 Python 對象：一次通透的序列化與反序列化之旅

目錄 一、為什么要談 JSON 二、最快速上手&#xff1a;兩把鑰匙 dumps 與 loads 三、深入 dumps&#xff1a;參數是魔法棒 四、深入 loads&#xff1a;把風險擋在門外 五、文件級序列化&#xff1a;dump 與 load 六、處理中文與編碼陷阱 七、異常場景與調試技巧 八、實…
閱讀更多...
Leetcode 3315. 構造最小位運算數組 II

Leetcode 3315. 構造最小位運算數組 II

1.題目基本信息 1.1.題目描述 給你一個長度為 n 的質數數組 nums 。你的任務是返回一個長度為 n 的數組 ans &#xff0c;對于每個下標 i &#xff0c;以下 條件 均成立&#xff1a; ans[i] OR (ans[i] 1) nums[i] 除此以外&#xff0c;你需要 最小化 結果數組里每一個 a…
閱讀更多...
黑搜小知識 | DNS域名解析過程是什么樣的？

黑搜小知識 | DNS域名解析過程是什么樣的？

什么是DNS&#xff1f;DNS( Domain Name System)是“域名系統”的英文縮寫&#xff0c;是一種組織成域層次結構的計算機和網絡服務命名系統&#xff0c;它用于TCP/IP網絡&#xff0c;它所提供的服務是用來將主機名和域名轉換為IP地址的工作。舉例來說&#xff0c;如果你要訪問域…
閱讀更多...
MyBatis 使用教程及插件開發

MyBatis 使用教程及插件開發

作者&#xff1a;小凱 沉淀、分享、成長&#xff0c;讓自己和他人都能有所收獲&#xff01; 本文的宗旨在于通過簡單干凈實踐的方式教會讀者&#xff0c;使用 SpringBoot 配置 MyBatis 并完成對插入、批量插入、修改、查詢以及注解事務和編程事務的使用&#xff0c;通過擴展插件…
閱讀更多...
Maui勸退：用windows直接真機調試iOS，無須和Mac配對

Maui勸退：用windows直接真機調試iOS，無須和Mac配對

初級代碼游戲的專欄介紹與文章目錄-CSDN博客 我的github&#xff1a;codetoys&#xff0c;所有代碼都將會位于ctfc庫中。已經放入庫中我會指出在庫中的位置。 這些代碼大部分以Linux為目標但部分代碼是純C的&#xff0c;可以在任何平臺上使用。 源碼指引&#xff1a;github源…
閱讀更多...
【極客日常】后端任務動態注入執行策略的一種技術實現

【極客日常】后端任務動態注入執行策略的一種技術實現

近期做項目時遇到一個場景&#xff0c;是需要在后端任務執行時動態注入策略。具體而言&#xff0c;筆者負責的后端服務&#xff0c;可以理解是會在線上服務發布時&#xff0c;對服務風險做實時掃描&#xff0c;那么這個掃描就需要根據當前線上服務發布上下文&#xff0c;匹配對…
閱讀更多...
8. JVM類裝載的執行過程

8. JVM類裝載的執行過程

1. JVM介紹和運行流程-CSDN博客 2. 什么是程序計數器-CSDN博客 3. java 堆和 JVM 內存結構-CSDN博客 4. 虛擬機棧-CSDN博客 5. JVM 的方法區-CSDN博客 6. JVM直接內存-CSDN博客 7. JVM類加載器與雙親委派模型-CSDN博客 8. JVM類裝載的執行過程-CSDN博客 9. JVM垃圾回收…
閱讀更多...
Linux操作系統之信號：信號的產生

Linux操作系統之信號：信號的產生

前言&#xff1a;上篇文章我們大致講解了信號的有關概念&#xff0c;為大家引入了信號的知識點。但光知道那些是遠遠不夠的。本篇文章&#xff0c;我將會為大家自己的講解一下信號的產生的五種方式&#xff0c;希望對大家有所幫助。一、鍵盤&#xff08;硬件&#xff09;產生信…
閱讀更多...
pdf拆分

pdf拆分

文章目錄 背景目標實現下載 背景 好不容易下載的1000頁行業報告&#xff0c;領導非要按章節拆分成20份&#xff01;學術論文合集需要按作者拆分投稿&#xff0c;手動分頁到懷疑人生…客戶發來加密合同&#xff0c;要求每5頁生成獨立文檔&#xff0c;格式還不能亂&#xff01; …
閱讀更多...
vue3使用mermaid生成圖表，并可編輯

vue3使用mermaid生成圖表，并可編輯

效果圖實際代碼<template><div class"mermaid-container" style"z-index: 99999" ref"wrapperRef"><!-- 控制欄 --><div class"control-bar"><div class"control-bar-flex control-bar-tab-wrap"…
閱讀更多...
tcp/quic 的滑動窗口

tcp/quic 的滑動窗口

一、滑動窗口 rwnd&#xff1a; 接收端窗口&#xff0c;接收方在每次發送ACK確認報文時&#xff0c;會包含一個 rwnd (Receive Window Size) 字段&#xff0c;指明自己當前剩余的接收緩沖區大小&#xff08;即可用窗口&#xff09;&#xff0c;這里是否是socket的接收緩沖區&am…
閱讀更多...
JVM監控及診斷工具-命令行篇

JVM監控及診斷工具-命令行篇

18.1. 概述 性能診斷是軟件工程師在日常工作中需要經常面對和解決的問題&#xff0c;在用戶體驗至上的今天&#xff0c;解決好應用的性能問題能帶來非常大的收益。 Java 作為最流行的編程語言之一&#xff0c;其應用性能診斷一直受到業界廣泛關注。可能造成 Java 應用出現性能…
閱讀更多...
Jenkins 版本升級與插件問題深度復盤：從 2.443 到 2.504.3 及功能恢復全解析

Jenkins 版本升級與插件問題深度復盤：從 2.443 到 2.504.3 及功能恢復全解析

前言&#xff1a;問題溯源與升級必要性 在 Jenkins 持續集成體系中&#xff0c;插件生態是其強大功能的核心驅動力。然而&#xff0c;某次例行維護中&#xff0c;團隊對 Jenkins 2.443 環境的插件進行批量升級后&#xff0c;意外觸發連鎖反應 &#xff1a; SSH Server 插件功能…
閱讀更多...
最新文章

Copyright @ 2022~2023