hash connect by 导致结果集不同

问题描述

OceanBase 数据库中使用 /*+ USE_HASH(view2 view3) */ 强制走 hash connect by 算子，导致结果集错误。

示例如下:

1. 创建测试表。

   a. 创建测试表 eprk_trade_operate。

   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。

   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 插入测试数据。

   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)
   

   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)
   

   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)
   

   提交测试数据:

   obclient [SYS]> commit;
   Query OK, 0 rows affected (0.001 sec)
   

   b. 向表 eprk_workflow_step 插入测试数据。

   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)
   

   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)
   

   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)
   

   提交测试数据:

   obclient [SYS]> commit;
   Query OK, 0 rows affected (0.001 sec)
   

3. 执行测试语句。

   a. 添加 /+ USE_HASH(view2 view3)/ hint 提示:

   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;
   

   输出执行计划内容如下:

   | =========================================
   |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 提示:

   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;
   

   输出执行计划内容如下:

   | ================================================
   |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 提示。