--- title: hash connect by 导致结果集不同-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 hash connect by 导致结果集不同相关的常见问题和使用技巧，帮助您快速解决 hash connect by 导致结果集不同的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # hash connect by 导致结果集不同 更新时间：2023-11-14 03:16 适用版本： V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 OceanBase 数据库中使用 `/*+ USE_HASH(view2 view3) */` 强制走 hash connect by 算子，导致结果集错误。 示例如下: 1. 创建测试表。 a. 创建测试表 eprk_trade_operate。 ```shell obclient [SYS]> CREATE TABLE eprk_trade_operate(operate_code varchar(30), operate_name varchar(30), pk_trade_operate varchar(30)); Query OK, 0 rows affected (0.068 sec) ``` b. 创建测试表 eprk_workflow_step。 ```shell obclient [SYS]> CREATE TABLE eprk_workflow_step(PK_ORG varchar(30), pk_trade_operate varchar(30), pk_workflow_define varchar(30), next_step varchar(30), pk_workflow_step varchar(30)); Query OK, 0 rows affected (0.029 sec) ``` 2. 插入测试数据。 a. 向表 eprk_trade_operate 插入测试数据。 ```shell obclient [SYS]> INSERT INTO eprk_trade_operate (operate_code,operate_name, pk_trade_operate) VALUES('operate_code01', 'operate_name01', 'pk_trade_operate01'); Query OK, 1 row affected (0.001 sec) ``` ```shell obclient [SYS]> INSERT INTO eprk_trade_operate (operate_code,operate_name, pk_trade_operate) VALUES('operate_code02', 'operate_name02', 'pk_trade_operate02'); Query OK, 1 row affected (0.001 sec) ``` ```shell obclient [SYS]> INSERT INTO eprk_trade_operate (operate_code,operate_name, pk_trade_operate) VALUES('operate_code03', 'operate_name03', 'pk_trade_operate03'); Query OK, 1 row affected (0.001 sec) ``` 提交测试数据: ```shell obclient [SYS]> commit; Query OK, 0 rows affected (0.001 sec) ``` b. 向表 eprk_workflow_step 插入测试数据。 ```shell obclient [SYS]> INSERT INTO eprk_workflow_step (PK_ORG, pk_trade_operate , pk_workflow_define, next_step,pk_workflow_step) VALUES('pkorg01', ' pk_operate01', 'pk_define01', 'next_step01','next_workflow_step01'); Query OK, 1 row affected (0.004 sec) ``` ```shell obclient [SYS]> INSERT INTO eprk_workflow_step (PK_ORG, pk_trade_operate , pk_workflow_define, next_step,pk_workflow_step) VALUES('pkorg02', ' pk_operate02', 'pk_define02', 'next_step02',',next_workflow_step02'); Query OK, 1 row affected (0.001 sec) ``` ```shell obclient [SYS]> INSERT INTO eprk_workflow_step (PK_ORG, pk_trade_operate , pk_workflow_define, next_step,pk_workflow_step) VALUES('pkorg03', ' pk_operate03', 'pk_define03', 'next_step03','next_workflow_step03'); Query OK, 1 row affected (0.001 sec) ``` 提交测试数据: ``` 3. 执行测试语句。 a. 添加 /*+ USE_HASH(view2 view3)*/ hint 提示: ```shell obclient [SYS]> EXPLAIN SELECT /*+ USE_HASH(view2 view3)*/ tr.operate_code, tr.operate_name from eprk_workflow_step st INNER JOIN eprk_trade_operate tr ON st.pk_trade_operate = tr.pk_trade_operate CONNECT BY prior st.next_step = st.pk_workflow_step; ``` 输出执行计划内容如下: ```shell | ========================================= |ID|OPERATOR |NAME |EST. ROWS|COST| ----------------------------------------- |0 |HASH CONNECT BY| |8 |195 | |1 | SUBPLAN SCAN |VIEW2|3 |95 | |2 | MERGE JOIN | |3 |95 | |3 | SORT | |3 |47 | |4 | TABLE SCAN |ST |3 |46 | |5 | SORT | |3 |47 | |6 | TABLE SCAN |TR |3 |46 | |7 | SUBPLAN SCAN |VIEW3|3 |95 | |8 | MERGE JOIN | |3 |95 | |9 | SORT | |3 |47 | |10| TABLE SCAN |ST |3 |46 | |11| SORT | |3 |47 | |12| TABLE SCAN |TR |3 |46 | ========================================= Outputs & filters: ------------------------------------- 0 - output([VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]), filter(nil), equal_conds([VIEW2.ST.NEXT_STEP = VIEW3.ST.PK_WORKFLOW_STEP]), other_conds(nil) 1 - output([VIEW2.ST.PK_TRADE_OPERATE], [VIEW2.TR.PK_TRADE_OPERATE], [VIEW2.ST.NEXT_STEP], [VIEW2.ST.PK_WORKFLOW_STEP], [VIEW2.TR.OPERATE_CODE], [VIEW2.TR.OPERATE_NAME]), filter(nil), access([VIEW2.ST.PK_TRADE_OPERATE], [VIEW2.TR.PK_TRADE_OPERATE], [VIEW2.ST.NEXT_STEP], [VIEW2.ST.PK_WORKFLOW_STEP], [VIEW2.TR.OPERATE_CODE], [VIEW2.TR.OPERATE_NAME]) 2 - output([ST.PK_TRADE_OPERATE], [TR.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), equal_conds([ST.PK_TRADE_OPERATE = TR.PK_TRADE_OPERATE]), other_conds(nil) 3 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), sort_keys([ST.PK_TRADE_OPERATE, ASC]) 4 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), access([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), partitions(p0) 5 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), sort_keys([TR.PK_TRADE_OPERATE, ASC]) 6 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), access([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), partitions(p0) 7 - output([VIEW3.ST.PK_TRADE_OPERATE], [VIEW3.TR.PK_TRADE_OPERATE], [VIEW3.ST.NEXT_STEP], [VIEW3.ST.PK_WORKFLOW_STEP], [VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]), filter(nil), access([VIEW3.ST.PK_TRADE_OPERATE], [VIEW3.TR.PK_TRADE_OPERATE], [VIEW3.ST.NEXT_STEP], [VIEW3.ST.PK_WORKFLOW_STEP], [VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]) 8 - output([ST.PK_TRADE_OPERATE], [TR.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), equal_conds([ST.PK_TRADE_OPERATE = TR.PK_TRADE_OPERATE]), other_conds(nil) 9 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), sort_keys([ST.PK_TRADE_OPERATE, ASC]) 10 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), access([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), partitions(p0) 11 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), sort_keys([TR.PK_TRADE_OPERATE, ASC]) 12 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), access([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), partitions(p0) ``` b. 取消 /*+ USE_HASH(view2 view3)*/ hint 提示: ```shell obclient [SYS]> EXPLAIN SELECT tr.operate_code, tr.operate_name FROM eprk_workflow_step st INNER JOIN eprk_trade_operate tr ON st.pk_trade_operate = tr.pk_trade_operate CONNECT BY prior st.next_step = st.pk_workflow_step; ``` ```shell | ================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------ |0 |NESTED-LOOP CONNECT BY| |8 |383 | |1 | SUBPLAN SCAN |VIEW2|3 |95 | |2 | MERGE JOIN | |3 |95 | |3 | SORT | |3 |47 | |4 | TABLE SCAN |ST |3 |46 | |5 | SORT | |3 |47 | |6 | TABLE SCAN |TR |3 |46 | |7 | SUBPLAN SCAN |VIEW3|3 |95 | |8 | MERGE JOIN | |3 |95 | |9 | SORT | |3 |47 | |10| TABLE SCAN |ST |3 |46 | |11| SORT | |3 |47 | |12| TABLE SCAN |TR |3 |46 | ================================================ Outputs & filters: ------------------------------------- 0 - output([VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]), filter(nil), conds([VIEW2.ST.NEXT_STEP = VIEW3.ST.PK_WORKFLOW_STEP]), nl_params_(nil) 1 - output([VIEW2.ST.PK_TRADE_OPERATE], [VIEW2.TR.PK_TRADE_OPERATE], [VIEW2.ST.NEXT_STEP], [VIEW2.ST.PK_WORKFLOW_STEP], [VIEW2.TR.OPERATE_CODE], [VIEW2.TR.OPERATE_NAME]), filter(nil), access([VIEW2.ST.PK_TRADE_OPERATE], [VIEW2.TR.PK_TRADE_OPERATE], [VIEW2.ST.NEXT_STEP], [VIEW2.ST.PK_WORKFLOW_STEP], [VIEW2.TR.OPERATE_CODE], [VIEW2.TR.OPERATE_NAME]) 2 - output([ST.PK_TRADE_OPERATE], [TR.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), equal_conds([ST.PK_TRADE_OPERATE = TR.PK_TRADE_OPERATE]), other_conds(nil) 3 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), sort_keys([ST.PK_TRADE_OPERATE, ASC]) 4 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), access([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), partitions(p0) 5 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), sort_keys([TR.PK_TRADE_OPERATE, ASC]) 6 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), access([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), partitions(p0) 7 - output([VIEW3.ST.PK_TRADE_OPERATE], [VIEW3.TR.PK_TRADE_OPERATE], [VIEW3.ST.NEXT_STEP], [VIEW3.ST.PK_WORKFLOW_STEP], [VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]), filter(nil), access([VIEW3.ST.PK_TRADE_OPERATE], [VIEW3.TR.PK_TRADE_OPERATE], [VIEW3.ST.NEXT_STEP], [VIEW3.ST.PK_WORKFLOW_STEP], [VIEW3.TR.OPERATE_CODE], [VIEW3.TR.OPERATE_NAME]) 8 - output([ST.PK_TRADE_OPERATE], [TR.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), equal_conds([ST.PK_TRADE_OPERATE = TR.PK_TRADE_OPERATE]), other_conds(nil) 9 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), sort_keys([ST.PK_TRADE_OPERATE, ASC]) 10 - output([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), filter(nil), access([ST.PK_TRADE_OPERATE], [ST.NEXT_STEP], [ST.PK_WORKFLOW_STEP]), partitions(p0) 11 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), sort_keys([TR.PK_TRADE_OPERATE, ASC]) 12 - output([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), filter(nil), access([TR.PK_TRADE_OPERATE], [TR.OPERATE_CODE], [TR.OPERATE_NAME]), partitions(p0) ``` #### 说明 区别是生成的计划不同，加了 `/*+ USE_HASH(view2 view3)*/ ` 提示会生成 `HASH CONNECT BY`, 不加 `/*+ USE_HASH(view2 view3)*/ ` 提示生成 `NESTED-LOOP CONNECT BY`。 ## 适用版本 OceanBase 数据库 V2.x 和 V3.x 版本。 ## 问题原因 1. 正确性问题，加了 hash hint，导致 connect by 生成了错误类型的算子。 2. connect by 在右支有索引可用时，会利用这个索引，反复 rescan 右支，在右支存在时 PX，rescan 开销比较大。 ## 解决方法 不使用 /*+ USE_HASH(view2 view3)*/ hint 提示。 Previous [ORDER BY DESC 语句触发的正确性问题](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000008212) Next [OceanBase 数据库 MySQL 模式中 SELECT ... FOR UPDATE LIMIT 1 的加锁行为是怎样的](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000000130) ![有帮助](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) 咨询热线