OB运维 | 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 呢？

OceanBase 4.x 版本增加了 XMLAGG 函数。
OceanBase 4.x 版本已经修复了 WM_CONCAT 函数触发 remove order by改写的问题。

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

于11月 18, 202411月 18, 2024由OssActiontech发布

问题描述

数据库版本

分析过程

1. 获取原 SQL

原输出类型

适配改造

复现步骤

1. 测试环境复现

2. 对比执行计划

3. HINT 干预

结论

解决方案

OceanBase 4.x 呢？

OceanBase

OB 运维 | 关于 OceanBase 4.2.x 新增副本随机拉取问题的解决方案

OceanBase

OB 运维 | Oracle 迁移到 OB 过程中有哪些不兼容的 DDL 语法？

OceanBase

OceanBase 的 Oracle 模式竟然可以使用 Repeatable Read？

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

于11月 18, 202411月 18, 2024由OssActiontech发布

问题描述

数据库版本

分析过程

1. 获取原 SQL

原输出类型

适配改造

复现步骤

1. 测试环境复现

2. 对比执行计划

3. HINT 干预

结论

解决方案

OceanBase 4.x 呢？

相关文章

OceanBase

OB 运维 | 关于 OceanBase 4.2.x 新增副本随机拉取问题的解决方案

OceanBase

OB 运维 | Oracle 迁移到 OB 过程中有哪些不兼容的 DDL 语法？

OceanBase

OceanBase 的 Oracle 模式竟然可以使用 Repeatable Read？