--- title: 如何通过 ROWID 拆分脚本实现并发处理逻辑-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于如何通过 ROWID 拆分脚本实现并发处理逻辑相关的常见问题和使用技巧，帮助您快速解决如何通过 ROWID 拆分脚本实现并发处理逻辑的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 如何通过 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，可通过下述方法实现。 1. 把数据按 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) ``` 2. 利用上一步查到的 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 ``` 3. 并发调度脚本。 ``` 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)](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000659500) 下一篇 [OceanBase 数据库的关键字可以规避吗](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001054) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线