当前位置： 首页 > news >正文

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

news 2024/11/7 17:41:30

作者：杨敬博，爱可生 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_nameFROM tol_report_userORDER 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_DATAFULL(@"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_DATAFULL(@"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 nFROM AORDER 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_DATAFULL(@"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_nameFROM tol_report_userORDER BY inner_rule_file_name
) AS rt;

OceanBase 4.x 呢？

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

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle


http://www.mrgr.cn/news/67709.html

相关文章：

  • Ubuntu和Debian系列的Release默认shell解释器变更
  • UE5.4 PCG 用蓝图变量设置PCG的变量
  • 1.6K+ Star！Ichigo：一个开源的实时语音AI项目
  • 学习记录：基于Z-Stack 3.0.1的Zigbee智能插座实现
  • STM32——ADC
  • SQL拦截（二）InnerInterceptor
  • AP5001A AP系列模拟信号源
  • 标签权重的计算方法之贝叶斯平滑
  • Postman上传图片如何处理
  • M1M2 MAC安装windows11 虚拟机的全过程
  • Oracle 数据库特性一图快速了解
  • 元学习法Meta-Learner
  • 跨域及解决跨域
  • 受保护的视图取消方法
  • MQTT从入门到精通之MQTT进阶
  • 网站robots文件怎么优化？robots.txt文件的作用及生成
  • 【C++】封装红黑树实现mymap和myset
  • ADS软件操作（一）
  • 雷池社区版7.1新版本自定义NGINX配置分析
  • 策略梯度方法【Policy Gradient】
  • 阿里云函数计算GBK编码
  • 刚接收就被On Hold了，我的SCI还有救吗？
  • cuda 环境搭建
  • 移动应用病毒式营销：如何吸引数百万用户
  • 芯片低功耗设计实现upf编写指南（附低功耗项目案例）
  • Git进阶（十九）：git revert 导致 merge 代码丢失问题修复