作者：杨敬博，爱可生 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 呢？
- OceanBase 4.x 版本增加了
XMLAGG函数。 - OceanBase 4.x 版本已经修复了
WM_CONCAT函数触发remove order by改写的问题。