划线反馈

如何通过 ROWID 拆分脚本实现并发处理逻辑

更新时间：2026-05-29 08:46

适用版本：V2.1.x、V2.2.x、V3.1.x、V3.2.x内容类型：How-to

对于部分 OceanBase 数据库版本，如 OceanBase 数据库 V3.2.4，部分函数如 dbms_crypto.hash 会使得 sql 语句的并行失效，本文提供一种方法利用 rowid 进行逻辑拆分，以实现并发调度的目的。

本方法使用 rowid 进行数据分片，方便对于无主键的表进行合理的数据拆分。对于有主键表，因为 rowid 是主键的映射，也不影响本方法的有效性。

注意

建议按分区来进行数据的分片和并行处理，尽量避免跨分区的数据分片，以免出现结果不正确的问题。

适用版本

OceanBase 数据库 V2.x、V3.x 版本。

操作步骤

本示例为 OceanBase 数据库的 Oracle 租户，需要执行的语句如下。

select 'Re'||'cords: '||count(*)||' '||
 sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'
   ||'-'||SEQNUM
   ||'-'||rawtohex(FILETYPE)
   ||'-'||rawtohex(CHANNELID)
   ||'-'||rawtohex(SERVNUMBER)
   ||'-'||VERIFYTYPE
   ||'-'||rawtohex(PRODID)
   ||'-'||AMT
   ||'-'||rawtohex(EXTTID)
   ||'-'||rawtohex(OSPORDERID)
   ||'-'||rawtohex(ORDERID)
   ||'-'||rawtohex(ORDERSTATE)
   ||'-'||rawtohex(BUSITYPE)
   ||'-'||VALIDDATE
   ||'-'||EXPIREDATE
   ||'-'||RIGHTDATE
   ||'-'||STATEDATE
   ||'-'||rawtohex(FILENAME)
   ||'-'||HANDLESTATE
   ||'-'||DIFFTYPE
   ||'-'||rawtohex(HANDLEERRREASON)
   ||'-'||rawtohex(HANDLEMEMO)
   ||'-'||rawtohex(OPRCODE)
   ||'-'||OPRDATE
   ||'-'||rawtohex(RELATEDPRODID)
 ),2),24,8),'xxxxxxxx')) HASH_VAL 
 from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS;

由于此处使用了 dbms_crypto.hash 函数，此函数在 V3.2.4 版本中无法开并行，直接在 OceanBase 数据库中执行查询，效率较低，耗时约 17 分钟。

+-------------------------------------+ 
| HASH_VAL                            | 
+-------------------------------------+ 
| Records: 30932539 66431723718170404 | 
+-------------------------------------+ 
1 row in set (17 min 8.546 sec)

现在打算通过并发脚本优化此查询，初定并发度为 8，可通过下述方法实现。

把数据按 rowid 平均分成 8 份，选取每份数据的起始 rowid。

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 16;
select str from (
select ob_rowid, decode(mod(rownum,trunc((select count(*) from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS)/8)),0,''||rowid,'') str,rownum seq from (
select rowid ob_rowid from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS  order by 1
)
)
where str is not null
order by seq;

参考结果如下。

+-------------------+
| STR               |
+-------------------+
| *AAIKx/86AAAAAAA= |
| *AAIKjv91AAAAAAA= |
| *AAIKVf+wAAAAAAA= |
| *AAIKHP/rAAAAAAA= |
| *AAIK4/4mAQAAAAA= |
| *AAIKqv5hAQAAAAA= |
| *AAIKcf6cAQAAAAA= |
| *AAIKOP7XAQAAAAA= |
+-------------------+
8 rows in set (29.318 sec)

利用上一步查到的 rowid 生成分批处理的 SQL 语句（实际将数据分成 9 份，其中一份数据非常少）。

echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where  rowid<'*AAIKx/86AAAAAAA=';"                              >select_00.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKx/86AAAAAAA=' and rowid<'*AAIKjv91AAAAAAA=';">select_01.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKjv91AAAAAAA=' and rowid<'*AAIKVf+wAAAAAAA=';">select_02.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKVf+wAAAAAAA=' and rowid<'*AAIKHP/rAAAAAAA=';">select_03.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKHP/rAAAAAAA=' and rowid<'*AAIK4/4mAQAAAAA=';">select_04.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIK4/4mAQAAAAA=' and rowid<'*AAIKqv5hAQAAAAA=';">select_05.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKqv5hAQAAAAA=' and rowid<'*AAIKcf6cAQAAAAA=';">select_06.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKcf6cAQAAAAA=' and rowid<'*AAIKOP7XAQAAAAA=';">select_07.sql
echo "select 'Re'||'cords: '||count(*)||' '||sum(to_number(substr(dbms_crypto.hash(to_clob('Hash'||'-'||SEQNUM||'-'||rawtohex(FILETYPE)||'-'||rawtohex(CHANNELID)||'-'||rawtohex(SERVNUMBER)||'-'||VERIFYTYPE||'-'||rawtohex(PRODID)||'-'||AMT||'-'||rawtohex(EXTTID)||'-'||rawtohex(OSPORDERID)||'-'||rawtohex(ORDERID)||'-'||rawtohex(ORDERSTATE)||'-'||rawtohex(BUSITYPE)||'-'||VALIDDATE||'-'||EXPIREDATE||'-'||RIGHTDATE||'-'||STATEDATE||'-'||rawtohex(FILENAME)||'-'||HANDLESTATE||'-'||DIFFTYPE||'-'||rawtohex(HANDLEERRREASON)||'-'||rawtohex(HANDLEMEMO)||'-'||rawtohex(OPRCODE)||'-'||OPRDATE||'-'||rawtohex(RELATEDPRODID)),2),24,8),'xxxxxxxx')) HASH_VAL from NGCRM_XX.XXXXX_RIGHTSPRODIFHIS where rowid>='*AAIKOP7XAQAAAAA=';"                              >select_08.sql
echo "exit">>select_00.sql
echo "exit">>select_01.sql
echo "exit">>select_02.sql
echo "exit">>select_03.sql
echo "exit">>select_04.sql
echo "exit">>select_05.sql
echo "exit">>select_06.sql
echo "exit">>select_07.sql
echo "exit">>select_08.sql

并发调度脚本。

nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_00.sql">select_00.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_01.sql">select_01.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_02.sql">select_02.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_03.sql">select_03.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_04.sql">select_04.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_05.sql">select_05.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_06.sql">select_06.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_07.sql">select_07.sql.out 2>&1 &
nohup obclient -h192.168.1.146 -usys@xxcrm#XXCRM -P2883 -xxxxx__ -vvv -e "source select_08.sql">select_08.sql.out 2>&1 &

上述脚本结果如下。

批次	结果	哈希值
0	3866566	8304443979994892
1	3866567	8304529241408050
2	3866567	8304255003154290
3	3866567	8302695096423795
4	3866567	8303324444249202
5	3866567	8300202138763435
6	3866567	8305907433210301
7	3866567	8306359855298998
8	4	6525667441

总的记录数为 30932539，总的哈希值为 66431723718170404，与单个脚本结果一致，其中耗时最长的脚本时间为 3 min 32.330 sec，低于原来的 17 min 8.546 sec。

observer 日志打印 Transformer: iteration count overflow (MAX_ITERATION_COUNT=10, ret=0)

OceanBase 数据库的关键字可以规避吗

OceanBase 数据库