Oracle 遷移到 OB 過程中的函式改造案例

爱可生开源社区發表於2024-11-07
Oracle函式

作者：楊敬博，愛可生 DBA 團隊成員，一位會攝影、會鏟屎、會打球、會騎車、生活可以自理的 DBA。

審校及補充：胡呈清，官永強，程柳潤。

愛可生開源社群出品，原創內容未經授權不得隨意使用，轉載請聯絡小編並註明來源。

本文約 1300 字，預計閱讀需要 4 分鐘。

問題描述

客戶源資料庫（Oracle）中有使用 XMLAGG 函式對列拼接的需求。透過查詢官方文件發現 OceanBase 3.x 版本不支援 XMLAGG 相關函式，故使用 WM_CONCAT 函式進行適配改造。在初步改造後發現實際輸出結果並沒有排序，透過加 HINT 進行改造最佳化後，實現與預期一致的結果。

資料庫版本

  • OceanBase 3.2.3
  • Oracle11g

分析過程

1. 獲取原 SQL

SELECT xmlagg(xmlparse(content tr.inner_rule_file_name || ','
                       wellformed)
             order by tr.inner_rule_file_name)
       .getclobval()
FROM tol_report_user tr;

原輸出型別

在 Oracle 中 XML 函式輸出為 CLOB 型別 的結果。

適配改造

由於 OceanBase 3.x 不支援該函式，故使用 WM_CONCAT 函式進行適配改造。

SELECT WM_CONCAT(rt.inner_rule_file_name)
FROM (
    SELECT inner_rule_file_name
    FROM tol_report_user
    ORDER BY inner_rule_file_name
) AS rt;

SQL 改造後語意：先在子查詢中對需要拼接的欄位進行排序，再對排序後的結果進行拼接。可結果發現雖然子查詢中加了排序，但是在拼接後卻不是排序後的結果。

復現步驟

1. 測試環境復現

--測試表：
create table A 
(id number,
name varchar2(1000),
age number);

--測試資料：
insert into A values (1,'001.txt',29);
insert into A values (2,'002.pdf',19);
insert into A values (1,'001.txt',29);
insert into A values (2,'001.pdf',19);
insert into A values (3,'003.ppt',19);
insert into A values (3,'檔案.ppt',19);
insert into A values (3,'檔案.ppt',19);
insert into A (id,age)values (4,19);
insert into A (id,age)values (5,19);
commit;

--測試WM_CONCAT函式：
select  WM_CONCAT(a1.name) from (select name from A order by name) a1;

--測試結果
obclient [JINGBO]> select * from A;
+------+------------+------+
| ID   | NAME       | AGE  |
+------+------------+------+
|    1 | 001.txt    |   29 |
|    2 | 002.pdf    |   19 |
|    1 | 001.txt    |   29 |
|    2 | 001.pdf    |   19 |
|    3 | 003.ppt    |   19 |
|    3 | 檔案.ppt   |   19 |
|    3 | 檔案.ppt   |   19 |
|    4 | NULL       |   19 |
|    5 | NULL       |   19 |
+------+------------+------+
9 rows in set (0.009 sec)
obclient [JINGBO]> select  WM_CONCAT(a1.name) from (select name from A order by name) a1;
+---------------------------------------------------------------+
| WM_CONCAT(A1.NAME)                                            |
+---------------------------------------------------------------+
| 001.txt,002.pdf,001.txt,001.pdf,003.ppt,檔案.ppt,檔案.ppt     |
+---------------------------------------------------------------+
1 row in set (0.002 sec)
obclient [JINGBO]> select  WM_CONCAT(A.name) from A;
+---------------------------------------------------------------+
| WM_CONCAT(A.NAME)                                             |
+---------------------------------------------------------------+
| 001.txt,002.pdf,001.txt,001.pdf,003.ppt,檔案.ppt,檔案.ppt     |
+---------------------------------------------------------------+
1 row in set (0.008 sec)

測試後發現，實際輸出結果沒有排序，有子查詢與沒有子查詢的輸出結果是一致的。

2. 對比執行計劃

沒加子查詢的。

obclient [JINGBO]> explain extended select  WM_CONCAT(name) as a from A\G
**** 1. row ****
Query Plan: ========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST|
----------------------------------------
|0 |SCALAR GROUP BY|    |1        |47  |
|1 | TABLE SCAN    |A   |9        |46  |
========================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_WM_CONCAT(A.NAME(0x7fbb3fad3b30))(0x7fbb3fad3420)]), filter(nil),
      group(nil), agg_func([T_FUN_WM_CONCAT(A.NAME(0x7fbb3fad3b30))(0x7fbb3fad3420)])
  1 - output([A.NAME(0x7fbb3fad3b30)]), filter(nil),
      access([A.NAME(0x7fbb3fad3b30)]), partitions(p0),
      is_index_back=false,
      range_key([A.__pk_increment(0x7fbb3fba2ac0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
  /*+
  */
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "JINGBO.A"@"SEL$1")
      END_OUTLINE_DATA
  */
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
A:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[A], estimation info[table_id:1100611139454009, (table_type:1, version:0-1695319210321548-1695319210321548, logical_rc:0, physical_rc:0), (table_type:0, version:1695319210321548-1695319210321548-9223372036854775807, logical_rc:9, physical_rc:9)]
Parameters
-------------------------------------
1 row in set (0.005 sec)

加了子查詢的。

obclient [JINGBO]> explain extended select  WM_CONCAT(a1.n) as a from  (select name as n from A order by name) a1\G
**** 1. row ****
Query Plan: ========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST|
----------------------------------------
|0 |SCALAR GROUP BY|    |1        |47  |
|1 | TABLE SCAN    |A   |9        |46  |
========================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_WM_CONCAT(A.NAME(0x7fbb572d4580))(0x7fbb572d6cb0)]), filter(nil),
      group(nil), agg_func([T_FUN_WM_CONCAT(A.NAME(0x7fbb572d4580))(0x7fbb572d6cb0)])
  1 - output([A.NAME(0x7fbb572d4580)]), filter(nil),
      access([A.NAME(0x7fbb572d4580)]), partitions(p0),
      is_index_back=false,
      range_key([A.__pk_increment(0x7fbb573e7bc0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
  /*+
  */
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "JINGBO.A"@"SEL$1")
      END_OUTLINE_DATA
  */
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
A:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[A], estimation info[table_id:1100611139454009, (table_type:1, version:0-1695319210321548-1695319210321548, logical_rc:0, physical_rc:0), (table_type:0, version:1695319210321548-1695319210321548-9223372036854775807, logical_rc:9, physical_rc:9)]
Parameters
-------------------------------------
1 row in set (0.008 sec)

分別檢視加了子查詢與沒加子查詢的 SQL 執行計劃，發現執行計劃也是一致的：加了排序的子查詢也沒有出現排序的運算元。

3. HINT 干預

加 no_rewrite 進行干預，結果正常：

SELECT　/*+ NO_REWRITE */ WM_CONCAT(a1.n) as a
FROM (
    SELECT name as n
    FROM A
    ORDER BY name
) a1;

obclient [JINGBO]> select /*+ NO_REWRITE */ WM_CONCAT(a1.n) as a from  (select name as n from A order by name) a1;
+---------------------------------------------------------------+
| A                                                             |
+---------------------------------------------------------------+
| 001.pdf,001.txt,001.txt,002.pdf,003.ppt,檔案.ppt,檔案.ppt     |
+---------------------------------------------------------------+
1 row in set (0.001 sec)

執行計劃中有排序操作（SORT 運算元）：

obclient [JINGBO]> explain extended select /*+ NO_REWRITE */ WM_CONCAT(a1.n) as a from  (select name as n from A order by name) a1\G
**** 1. row ****
Query Plan: ========================================
|ID|OPERATOR       |NAME|EST. ROWS|COST|
----------------------------------------
|0 |SCALAR GROUP BY|    |1        |52  |
|1 | SUBPLAN SCAN  |A1  |9        |52  |
|2 |  SORT         |    |9        |52  |
|3 |   TABLE SCAN  |A   |9        |46  |
========================================
Outputs & filters:
-------------------------------------
  0 - output([T_FUN_WM_CONCAT(A1.N(0x7fbaa10c0590))(0x7fbaa10bfe80)]), filter(nil),
      group(nil), agg_func([T_FUN_WM_CONCAT(A1.N(0x7fbaa10c0590))(0x7fbaa10bfe80)])
  1 - output([A1.N(0x7fbaa10c0590)]), filter(nil),
      access([A1.N(0x7fbaa10c0590)])
  2 - output([A.NAME(0x7fbaa11d0ae0)]), filter(nil), sort_keys([A.NAME(0x7fbaa11d0ae0), ASC])
  3 - output([A.NAME(0x7fbaa11d0ae0)]), filter(nil),
      access([A.NAME(0x7fbaa11d0ae0)]), partitions(p0),
      is_index_back=false,
      range_key([A.__pk_increment(0x7fbaa11d6590)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
  /*+
      NO_REWRITE(@"SEL$1")
  */
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "JINGBO.A"@"SEL$2")
      NO_REWRITE(@"SEL$1")
      END_OUTLINE_DATA
  */
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
A:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[A], estimation info[table_id:1100611139454009, (table_type:1, version:0-1695319210321548-1695319210321548, logical_rc:0, physical_rc:0), (table_type:0, version:1695319210321548-1695319210321548-9223372036854775807, logical_rc:9, physical_rc:9)]
Parameters
-------------------------------------
1 row in set (0.134 sec)

加了 /*+ NO_REWRITE */ 符合預期輸出：先對子查詢中的結果排序，再對排序後的結果拼接。

結論

在 OceanBase 3.x 中使用 WM_CONCAT 函式，會觸發 remove order by 改寫，導致結果順序不一致，需要加 HINT 對 SQL 進行改造。

解決方案

SELECT　/*+ NO_REWRITE */ WM_CONCAT(rt.inner_rule_file_name)
FROM (
    SELECT inner_rule_file_name
    FROM tol_report_user
    ORDER BY inner_rule_file_name
) AS rt;

OceanBase 4.x 呢？

  1. OceanBase 4.x 版本增加了 XMLAGG 函式。
  2. OceanBase 4.x 版本已經修復了 WM_CONCAT 函式觸發 remove order by改寫的問題。

相關文章