Oracle 迁移到 OB 过程中的函数改造案例

作者：杨敬博，爱可生 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改写的问题。