strongduanmu commented on issue #22432:
URL:
https://github.com/apache/shardingsphere/issues/22432#issuecomment-1343735628
Latest result after optimizing sql decider logic:
```sql
sharding_db=> SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND
s_quantity < 100 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 = 1 AND d_id = 1
)
) AS L;sharding_db(> sharding_db(> sharding_db(> sharding_db(>
low_stock
-----------
0
(1 row)
[INFO ] 2022-12-09 09:57:41.875 [Connection-3-ThreadExecutor]
ShardingSphere-SQL - Logic SQL: SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND
s_quantity < 100 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 = 1 AND d_id = 1
)
) AS L;
[INFO ] 2022-12-09 09:57:41.875 [Connection-3-ThreadExecutor]
ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT count(*) AS low_stock FROM (
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 1 AND
s_quantity < 100 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 = 1 AND d_id = 1
)
) AS L;
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
