Oracle XQuery 實戰 (三)：外部數據源查詢、性能調優與執行計劃分析

一、查詢外部數據源：HTTP、文件系統、Repository 統一訪問

XQuery 可處理**任意位置**的 XML 數據：數據庫內、HTTP URL、本地文件、Repository 資源。但 **Oracle XML DB 對數據庫內數據有極致優化** (XPath Rewrite、XMLIndex)，若可控建議先入庫。

二、實戰案例：合併員工表與外部獎金 XML

場景

• 內部數據：HR.EMPLOYEES 表 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
• 外部數據：HTTP 發布的 empsbonus.xml (EMPNO, BONUS)
• 目標：生成合併 XML (EMPNO, FULL_NAME, BONUS)

empsbonus.xml 範例

  1001200
  1011000

XQuery 合併查詢

SELECT XMLQuery(
  'for $k in 1
   return  {
     for $i in ora:view("employees")/ROW,
         $j in $emps/EMPLOYEES/EMPLOYEE
     where $i/EMPLOYEE_ID = $j/EMPNO
     return 
       {xs:string($i/EMPLOYEE_ID)}
       {xs:string(fn:concat($i/FIRST_NAME, " ", $i/LAST_NAME))}
       {xs:integer($j/BONUS)}
     
   } '
  PASSING XMLParse(DOCUMENT HTTPURIType('https://localhost/employees/empsbonus.xml').getCLOB()) AS "emps"
  RETURNING CONTENT).GetStringVal() AS RESULT
FROM DUAL;

輸出結果

  100Steven King1200
  101Neena Kochhar1000

關鍵技術點

• ora:view("employees")：將關係表暴露為 XML 視圖 (每行成 ROW 元素)
• HTTPURIType(...).getCLOB()：HTTP 獲取外部 XML，轉為 CLOB 傳入
• PASSING ... AS "emps"：綁定外部 XML 為 XQuery 變數 $emps
• xs:string(), xs:integer()：顯式類型構造器確保輸出類型正確

三、性能調優：存儲模型決定優化上限

鐵律：高性能 XQuery 必須建立在 **關係數據** 或 **結構化 XMLType** 之上。

四、執行計劃分析：從 EXPLAIN PLAN 看優化效果

啟用 AUTOTRACE

-- 需先建立 PLUSTRACE 角色並授權
SET AUTOTRACE ON EXPLAIN

案例 1：未優化的 XPath 寫法

SELECT COUNT(*)
FROM oe.purchaseorder, XMLTable(
  'for $i in /PurchaseOrder/User
   where $i = "CJOHNSON"
   return $i'
  PASSING OBJECT_VALUE) ptab;

-- 執行計劃關鍵行:
-- | 2 | NESTED LOOPS | | 10782 | 2379K | 29 | -- 掃描 10782 行
-- | 3 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 226 | 5 |
-- | 4 | COLLECTION ITERATOR | XMLSEQUENCEFROMXMLTYPE | | | |

問題：XPath /PurchaseOrder/User 迭代 XML 內部節點，**未下推到關係層**，導致全表掃描 + XML 解析。

案例 2：重構後的優化寫法

SELECT COUNT(*)
FROM oe.purchaseorder, XMLTable(
  'for $i in /PurchaseOrder
   where $i/User = "CJOHNSON"
   return $i/User'
  PASSING OBJECT_VALUE) ptab;

-- 執行計劃關鍵行:
-- | 2 | NESTED LOOPS | | 1 | 29 | 7 | -- 僅 1 行
-- | 3 | FAST DUAL | | 1 | | 2 |
-- | 4 | TABLE ACCESS FULL | PURCHASEORDER | 1 | 29 | 5 |
-- Predicate: "PURCHASEORDER"."SYS_NC00022$"='CJOHNSON' -- 虛擬列直接過濾

優化點：迭代 **頂層 /PurchaseOrder** (對應關係表每一行)，WHERE 條件下推為虛擬列 SYS_NC00022$ (User 欄位) 直接過濾，**XPath Rewrite 成功**。

核心洞察

XQuery 寫法微小差異 → 執行計劃天壤之別。開發階段 **必須配合 EXPLAIN PLAN/AUTOTRACE** 驗證 XPath Rewrite 是否生效，關鍵指標：

• 是否出現 COLLECTION ITERATOR / XMLSEQUENCEFROMXMLTYPE (未重寫)
• 是否出現虛擬列謂詞 SYS_NCxxxxx$ (已重寫)
• 估算行數 (Rows) 是否合理

五、調優工具鏈建議

1. SQL Developer / TOAD：圖形化 EXPLAIN PLAN
2. DBMS_XPLAN.DISPLAY_CURSOR：運行時計劃含實際行數
3. XMLIndex / 結構化存儲：大 XML 文檔的加速器
4. SQL Plan Baseline：固化優化計劃防退化