已关闭
[Bug]: 开启rbp下，倒仓后进行analysis失败导致tpcc不走索引 #313
刘凯创建于 8月5日关闭于 8月15日
8月5日 添加了label：bug
opengauss_bot
8月5日 评论：
8月5日 评论：
This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.
8月5日 将 TestManager 设为负责人
8月5日 添加了label：sig/StorageEngine
opengauss_bot
8月5日 评论：
8月5日 评论：
Welcome To openGauss Community
Hey @Miyano , thanks for your contribution to the community.
Bot Usage Manual
I'm the Bot here serving you. You can find the instructions on how to interact with me at Here . That means you can comment below every pull request or issue to trigger Bot Commands.
Contact Guide
If you have any questions, please contact the SIG: StorageEngine ,
and any of the maintainers: @CarrotGo, @chendong76, @chenxiaobin19, @congzhou2603, @dodders, @hwworkholic, @jemappellehc, @muyulinzhong, @quemingjian, @shenzheng4, @shirley_zhengx, @superlchf, @totaj, @wlff234, @wofanzheng, @ywzq1161327784 ,
and any of the committers: @CityYard, @libiao2024, @lijieac, @theothersideofsea, @wang_bowen, @weithu, @xiong_xjun, @yuanyazhi, @zhangzq131 .
8月5日 修改标题为 “[Bug]: 开启rbp下，倒仓后进行analysis失败导致tpcc不走索引”，原标题为“[Bug]: 开启rbp下，analysis失败导致tpcc不走索引”
8月5日 移除了负责人 TestManager
opengauss_bot
8月5日 评论：
8月5日 评论：
This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.
8月5日 将 TestManager 设为负责人
此处折叠了5条事件消息 查看更多
8月8日 issue状态由 待办的 改变为 已完成
刘凯
8月12日 评论：
8月12日 评论：
B018自验
SQL> explain SELECT c_discount, c_last, c_credit, w_tax FROM bmsql_customer JOIN bmsql_warehouse ON (w_id = c_w_id) WHERE c_w_id = '28' AND c_d_id = '8' AND c_id = '2812';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 10 | 8 | | |
| 1 | NESTED LOOPS | | | 1 | 10 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[29,30) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 3 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
| 4 | TABLE ACCESS BY INDEX ROWID Filter:[0,1002) | TPCC | BMSQL_WAREHOUSE | 1000 | 2 | 0 | | |
| 5 | INDEX UNIQUE SCAN | TPCC | WAREHOUSE_INDEX | 1000 | 2 | 0 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: C_D_ID = '8' AND C_W_ID = '28' AND C_ID = '2812'
5 - access: W_ID = C_W_ID
14 rows fetched.
SQL> explain SELECT i_price, i_name, i_data FROM bmsql_item WHERE i_id = '64085';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 4 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[714,715) | TPCC | BMSQL_ITEM | 1 | 4 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | ITEM_INDEX | 1 | 4 | 4 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: I_ID = '64085'
10 rows fetched.
SQL> explain SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = '22';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 10 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[23,24) | TPCC | BMSQL_WAREHOUSE | 1 | 10 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | WAREHOUSE_INDEX | 1 | 10 | 4 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: W_ID = '22'
10 rows fetched.
SQL> explain SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = '52' AND d_id = '9';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 4 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[53,54) | TPCC | BMSQL_DISTRICT | 1 | 4 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | DISTRICT_INDEX | 1 | 4 | 4 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: D_W_ID = '52' AND D_ID = '9'
10 rows fetched.
SQL> explain SELECT c_id FROM bmsql_customer WHERE c_w_id = '26' AND c_d_id = '9' AND c_last = 'ABLEESEESE' ORDER BY c_first;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 14 | 20 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[27,28) | TPCC | BMSQL_CUSTOMER | 14 | 20 | 12 | | |
| 2 | INDEX RANGE SCAN | TPCC | BMSQL_CUSTOMER_IDX1 | 14 | 20 | 12 | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_LAST = 'ABLEESEESE' AND C_D_ID = '9' AND C_W_ID = '26'
10 rows fetched.
SQL> explain SELECT c_data FROM bmsql_customer WHERE c_w_id = '44' AND c_d_id = '2' AND c_id = '909';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[45,46) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_W_ID = '44' AND C_D_ID = '2' AND C_ID = '909'
10 rows fetched.
SQL> explain SELECT c_id FROM bmsql_customer WHERE c_w_id = '52' AND c_d_id = '10' AND c_last = 'EINGOUGHTPRES' ORDER BY c_first;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 10 | 17 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[53,54) | TPCC | BMSQL_CUSTOMER | 10 | 17 | 12 | | |
| 2 | INDEX RANGE SCAN | TPCC | BMSQL_CUSTOMER_IDX1 | 10 | 17 | 12 | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_LAST = 'EINGOUGHTPRES' AND C_D_ID = '10' AND C_W_ID = '52'
10 rows fetched.
SQL> explain SELECT c_first, c_middle, c_last, c_balance FROM bmsql_customer WHERE c_w_id = '4' AND c_d_id = '7' AND c_id = '171';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[5,6) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_W_ID = '4' AND C_D_ID = '7' AND C_ID = '171'
10 rows fetched.
SQL> explain SELECT o_id, o_entry_d, o_carrier_id FROM bmsql_oorder WHERE o_w_id = '4' AND o_d_id = '7' AND o_c_id = '171' ORDER BY o_id DESC LIMIT 1;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 8 | 8 | | |
| 1 | QUERY LIMIT | | | 1 | 8 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[5,6) | TPCC | BMSQL_OORDER | 1 | 8 | 8 | | |
| 3 | INDEX RANGE SCAN DESCENDING | TPCC | BMSQL_OORDER_IDX1 | 1 | 8 | 8 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: O_W_ID = '4' AND O_D_ID = '7' AND O_C_ID = '171'
11 rows fetched.
SQL> explain SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = '4' AND ol_d_id = '7' AND ol_o_id = '171' ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 | 12 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[5,6) | TPCC | BMSQL_ORDER_LINE | 4 | 12 | 12 | | |
| 2 | INDEX RANGE SCAN | TPCC | ORDER_LINE_INDEX | 4 | 12 | 12 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: OL_W_ID = '4' AND OL_D_ID = '7' AND OL_O_ID = '171'
10 rows fetched.
SQL> explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = '23' AND s_quantity < '18' AND s_i_id IN (SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = '23' AND d_id = '3')) AS L;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 75 | 16 | | |
| 1 | AGGR | | | 1 | 75 | 16 | | |
| 2 | NESTED LOOPS | | | 1 | 59 | 16 | | |
| 3 | SUBSELECT | | $FROM_SQ_0 | 1 | 47 | 0 | | |
| 4 | HASH DISTINCT | | | 1 | 55 | 4 | | |
| 5 | NESTED LOOPS | | | 1 | 47 | 4 | | |
| 6 | TABLE ACCESS BY INDEX ROWID Filter:[24,25) | TPCC | BMSQL_DISTRICT | 1 | 4 | 4 | | |
| 7 | INDEX UNIQUE SCAN | TPCC | DISTRICT_INDEX | 1 | 4 | 4 | | |
| 8 | TABLE ACCESS BY INDEX ROWID Filter:[0,1002) | TPCC | BMSQL_ORDER_LINE | 100000000 | 21 | 0 | | |
| 9 | INDEX RANGE SCAN | TPCC | ORDER_LINE_INDEX | 100000000 | 21 | 0 | | |
| 10 | TABLE ACCESS BY INDEX ROWID Filter:[24,25) | TPCC | BMSQL_STOCK | 9920 | 12 | 12 | | |
| 11 | INDEX UNIQUE SCAN | TPCC | STOCK_INDEX | 9920 | 12 | 12 | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
7 - access: D_W_ID = '23' AND D_ID = '3'
9 - access: OL_W_ID = D_W_ID AND OL_D_ID = D_ID AND OL_O_ID < D_NEXT_O_ID AND OL_O_ID >= D_NEXT_O_ID - 20
10 - filter: S_W_ID = '23' AND S_QUANTITY < '18'
11 - access: S_I_ID = BMSQL_ORDER_LINE.OL_I_ID
22 rows fetched.
SQL> explain SELECT no_o_id FROM bmsql_new_order WHERE no_w_id = '37' AND no_d_id = '8' ORDER BY no_o_id ASC;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 900 | 23 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ONLY Filter:[38,39) | TPCC | BMSQL_NEW_ORDER | 900 | 23 | 8 | | |
| 2 | INDEX RANGE SCAN | TPCC | NEW_ORDER_INDEX | 900 | 23 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: NO_W_ID = '37' AND NO_D_ID = '8'
10 rows fetched.
SQL> explain SELECT o_c_id FROM bmsql_oorder WHERE o_w_id = '34' AND o_d_id = '1' AND o_id = '2745';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[35,36) | TPCC | BMSQL_OORDER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | OORDER_INDEX | 1 | 8 | 8 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: O_W_ID = '34' AND O_D_ID = '1' AND O_ID = '2745'
10 rows fetched.
SQL> explain SELECT sum(ol_amount) AS sum_ol_amount FROM bmsql_order_line WHERE ol_w_id = '7' AND ol_d_id = '7' AND ol_o_id = '2716';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 24 | 12 | | |
| 1 | AGGR | | | 1 | 24 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[8,9) | TPCC | BMSQL_ORDER_LINE | 3 | 12 | 12 | | |
| 3 | INDEX RANGE SCAN | TPCC | ORDER_LINE_INDEX | 3 | 12 | 12 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: OL_W_ID = '7' AND OL_D_ID = '7' AND OL_O_ID = '2716'
11 rows fetched.
SQL> explain DELETE FROM bmsql_oorder WHERE o_id = '3745' AND o_d_id = '6' AND o_w_id = '38';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 0 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ONLY Filter:[39,40) | TPCC | BMSQL_OORDER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | OORDER_INDEX | 1 | 8 | 8 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: O_ID = '3745' AND O_D_ID = '6' AND O_W_ID = '38'
10 rows fetched.
SQL> explain DELETE FROM bmsql_new_order WHERE no_o_id = '3745' AND no_d_id = '6' AND no_w_id = '38';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 0 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ONLY Filter:[39,40) | TPCC | BMSQL_NEW_ORDER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | NEW_ORDER_INDEX | 1 | 8 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: NO_O_ID = '3745' AND NO_D_ID = '6' AND NO_W_ID = '38'
10 rows fetched.
SQL> explain DELETE FROM bmsql_order_line WHERE ol_o_id = '3001' AND ol_d_id = '7' AND ol_w_id = '40' AND ol_number = '9';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
----------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 0 | 12 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ONLY Filter:[41,42) | TPCC | BMSQL_ORDER_LINE | 1 | 12 | 12 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | ORDER_LINE_INDEX | 1 | 12 | 12 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: OL_O_ID = '3001' AND OL_D_ID = '7' AND OL_W_ID = '40' AND OL_NUMBER = '9'
10 rows fetched.
SQL> explain INSERT INTO bmsql_oorder (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES ('3745', '6', '38', '2377', '2019-09-04 09:18:36.966000 -04:00:00', '7', '1');
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 1 | 0 | 0 | | |
| 1 | LOAD TABLE CONVENTIONAL (Filter:id=39) | TPCC | BMSQL_OORDER | 1 | 0 | 0 | | |
-------------------------------------------------------------------------------------------------------------------
6 rows fetched.
SQL> explain INSERT INTO bmsql_new_order (no_o_id, no_d_id, no_w_id) VALUES ('3745', '6', '38');
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 1 | 0 | 0 | | |
| 1 | LOAD TABLE CONVENTIONAL (Filter:id=39) | TPCC | BMSQL_NEW_ORDER | 1 | 0 | 0 | | |
----------------------------------------------------------------------------------------------------------------------
6 rows fetched.
SQL> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = '59' AND s_i_id = '23024' FOR UPDATE;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 12 | 12 | | |
| 1 | FOR UPDATE | | | 1 | 12 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[60,61) | TPCC | BMSQL_STOCK | 1 | 12 | 12 | | |
| 3 | INDEX UNIQUE SCAN | TPCC | STOCK_INDEX | 1 | 12 | 12 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: S_W_ID = '59' AND S_I_ID = '23024'
11 rows fetched.
SQL> explain UPDATE bmsql_stock SET s_quantity = '24', s_ytd = s_ytd + '6', s_order_cnt = s_order_cnt + 1, s_remote_cnt = s_remote_cnt + '0' WHERE s_w_id = '55' AND s_i_id = '95627';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 12 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[56,57) | TPCC | BMSQL_STOCK | 1 | 12 | 12 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | STOCK_INDEX | 1 | 12 | 12 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: S_W_ID = '55' AND S_I_ID = '95627'
10 rows fetched.
SQL> explain INSERT INTO bmsql_order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_dist_info) VALUES ('3001', '7', '40', '9', '99217', '40', '1', '7.23', 'sKf3Jde9sqk2tB1BCuipqe02');
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 1 | 0 | 0 | | |
| 1 | LOAD TABLE CONVENTIONAL (Filter:id=41) | TPCC | BMSQL_ORDER_LINE | 1 | 0 | 0 | | |
-----------------------------------------------------------------------------------------------------------------------
6 rows fetched.
SQL> explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = '50' AND c_d_id = '10' AND c_id = '1149' FOR UPDATE;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 8 | 8 | | |
| 1 | FOR UPDATE | | | 1 | 8 | 8 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[51,52) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 3 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: C_W_ID = '50' AND C_D_ID = '10' AND C_ID = '1149'
11 rows fetched.
SQL> explain UPDATE bmsql_warehouse SET w_ytd = w_ytd + '4262.64' WHERE w_id = '31';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
----------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 10 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[32,33) | TPCC | BMSQL_WAREHOUSE | 1 | 10 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | WAREHOUSE_INDEX | 1 | 10 | 4 | | |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: W_ID = '31'
10 rows fetched.
SQL> explain UPDATE bmsql_district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = '39' AND d_id = '7';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 4 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[40,41) | TPCC | BMSQL_DISTRICT | 1 | 4 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | DISTRICT_INDEX | 1 | 4 | 4 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: D_W_ID = '39' AND D_ID = '7'
10 rows fetched.
SQL> explain UPDATE bmsql_customer SET c_balance = c_balance - '3408.35', c_ytd_payment = c_ytd_payment + '3408.35', c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = '38' AND c_d_id = '9' AND c_id = '2701';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[39,40) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_W_ID = '38' AND C_D_ID = '9' AND C_ID = '2701'
10 rows fetched.
SQL> explain UPDATE bmsql_customer SET c_balance = c_balance - '2462.62', c_ytd_payment = c_ytd_payment + '2462.62', c_payment_cnt = c_payment_cnt + 1, c_data = 'C_ID=2124 C_D_ID=1 C_W_ID=21 D_ID=1 W_ID=21 H_AMOUNT=2462.62 gSJdTpekAF4acXk4lQDgzW2wUE1JaPa0U2tAljrYWL25eokEBXJvL4KZ4TXgSXe1Y05wgGQR3HZXI3TMz4cqwcgCSgdF9tDJaEcW2OksE6MLmmlY2717Fx6bUkWl8FV0B6LCbmbgVqvhpwtlb4EMVPHa0xkCsPaykWcvt2rrK1i4ZhVAEh4DlFXspkeWjFROZBghJIWeCVs8mffq5UArDRtL2f3bQt4QG5Uzd01tCYsmD9EbLPlZXQVOYZ8TXF3HHKcdVygXWBgXTpO6wgrhrSNju2hnKkBTm6bkQgaSYlz9pZXHZzclas9DI7WZtFSFmvK9pnVarlrMPbLvustuksK5qI47KMHPUDtjU0G6jwQ5jo4a4LCZXPeQ9dSGRvxHr3UN94B7pa0jNNDhKkl' WHERE c_w_id = '21' AND c_d_id = '1' AND c_id = '2124';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[22,23) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_W_ID = '21' AND C_D_ID = '1' AND C_ID = '2124'
10 rows fetched.
SQL> explain INSERT INTO bmsql_history (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES ('2124', '1', '21', '1', '21', '2019-09-04 09:18:37.471000 -04:00:00', '2462.62', 'oQkl6l WP1X9zmpzJ');
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | 1 | 0 | 0 | | |
| 1 | LOAD TABLE CONVENTIONAL (Filter:id=22) | TPCC | BMSQL_HISTORY | 1 | 0 | 0 | | |
--------------------------------------------------------------------------------------------------------------------
6 rows fetched.
SQL> explain UPDATE bmsql_oorder SET o_carrier_id = '6' WHERE o_w_id = '7' AND o_d_id = '7' AND o_id = '2716';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[8,9) | TPCC | BMSQL_OORDER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | OORDER_INDEX | 1 | 8 | 8 | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: O_W_ID = '7' AND O_D_ID = '7' AND O_ID = '2716'
10 rows fetched.
SQL> explain UPDATE bmsql_order_line SET ol_delivery_d = '2019-09-04 13:46:13.984000 -04:00:00' WHERE ol_w_id = '17' AND ol_d_id = '9' AND ol_o_id = '2214';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 8 | 12 | 12 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[18,19) | TPCC | BMSQL_ORDER_LINE | 8 | 12 | 12 | | |
| 2 | INDEX RANGE SCAN | TPCC | ORDER_LINE_INDEX | 8 | 12 | 12 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: OL_W_ID = '17' AND OL_D_ID = '9' AND OL_O_ID = '2214'
10 rows fetched.
SQL> explain UPDATE bmsql_customer SET c_balance = c_balance + '79249.66', c_delivery_cnt = c_delivery_cnt + 1 WHERE c_w_id = '7' AND c_d_id = '7' AND c_id = '490';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[8,9) | TPCC | BMSQL_CUSTOMER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | CUSTOMER_INDEX | 1 | 8 | 8 | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: C_W_ID = '7' AND C_D_ID = '7' AND C_ID = '490'
10 rows fetched.
SQL> explain SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = '12' AND d_id = '2' FOR UPDATE;
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 4 | 4 | | |
| 1 | FOR UPDATE | | | 1 | 4 | 4 | | |
| 2 | TABLE ACCESS BY INDEX ROWID Filter:[13,14) | TPCC | BMSQL_DISTRICT | 1 | 4 | 4 | | |
| 3 | INDEX UNIQUE SCAN | TPCC | DISTRICT_INDEX | 1 | 4 | 4 | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
3 - access: D_W_ID = '12' AND D_ID = '2'
11 rows fetched.
SQL> explain UPDATE bmsql_district SET d_ytd = d_ytd + '929.52' WHERE d_w_id = '42' AND d_id = '8';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | 1 | 4 | 4 | | |
| 1 | TABLE ACCESS BY INDEX ROWID Filter:[43,44) | TPCC | BMSQL_DISTRICT | 1 | 4 | 4 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | DISTRICT_INDEX | 1 | 4 | 4 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: D_W_ID = '42' AND D_ID = '8'
10 rows fetched.
SQL> explain DELETE FROM bmsql_new_order WHERE no_w_id = '34' AND no_d_id = '1' AND no_o_id = '2745';
EXPLAIN PLAN OUTPUT
----------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Owner | Name | Rows | Cost | StartCost | Bytes | Remark |
---------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | 0 | 8 | 8 | | |
| 1 | TABLE ACCESS BY INDEX ONLY Filter:[35,36) | TPCC | BMSQL_NEW_ORDER | 1 | 8 | 8 | | |
| 2 | INDEX UNIQUE SCAN | TPCC | NEW_ORDER_INDEX | 1 | 8 | 8 | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by id):
-----------------------------------------
2 - access: NO_W_ID = '34' AND NO_D_ID = '1' AND NO_O_ID = '2745'
10 rows fetched.8月12日 issue状态由 已完成 改变为 待回归
sungang14
8月13日 评论：
8月13日 评论：
sungang14
8月14日 评论：
8月14日 评论：
create user TPCC identified by 'Huawei@123';
grant create session to TPCC;
grant create table to TPCC;
grant dba to TPCC;
grant inherit privileges on user SYS to TPCC;
alter system set ENABLE_SYSDBA_REMOTE_LOGIN = TRUE;
alter system set ENABLE_SYS_REMOTE_LOGIN = TRUE;
analyze table bmsql_warehouse compute statistics;
analyze table bmsql_district compute statistics;
analyze table bmsql_customer compute statistics;
analyze table bmsql_history compute statistics;
analyze table bmsql_oorder compute statistics;
analyze table bmsql_new_order compute statistics;
analyze table bmsql_order_line compute statistics;
analyze table bmsql_stock compute statistics;
analyze table bmsql_item compute statistics;
alter system flush sqlpool;
sungang14
8月14日 评论：
8月14日 评论：
【回归人员】sungang14
【回归时间】2026.8.15
【回归版本】oGRAC 7.0.0B018
【回归结论】通过
【回归步骤】
tpcc导入1000仓数据，两次analyze和explain
重新导入数据
再次导入数据
8月15日 issue状态由 待回归 改变为 已验收
8月15日 关闭了 issue
测试类型
接口功能
测试版本
7.0.0-RC3
问题描述
操作系统和硬件信息
NAME="openEuler"
VERSION="20.03 (LTS-SP3)"
ID="openEuler"
VERSION_ID="20.03"
PRETTY_NAME="openEuler 20.03 (LTS-SP3)"
ANSI_COLOR="0;31"
建议在920机器上执行
测试环境
oGRAC双节点
被测功能
预置条件
操作步骤
预期输出
分析阶段成功，并且执行上述语句全部走索引
实际输出
日志信息
提单组织
内部开发
测试代码