作者：杨敬博，爱可生 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 trueUsed Hint:------------------------------------- /*+ */Outline Data:------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "JINGBO.A"@"SEL$1") END_OUTLINE_DATA */Plan Type:-------------------------------------LOCALOptimization 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 trueUsed Hint:------------------------------------- /*+ */Outline Data:------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "JINGBO.A"@"SEL$1") END_OUTLINE_DATA */Plan Type:-------------------------------------LOCALOptimization 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 trueUsed 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:-------------------------------------LOCALOptimization 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 呢？
- OceanBase 4.x 版本增加了
XMLAGG函数。 - OceanBase 4.x 版本已经修复了
WM_CONCAT函数触发remove order by改写的问题。