执行计划控制

OceanBase 技术问题
#1

测试环境：
版本：OceanBase_CE 4.2.1.10 (r110000072024112010-28c1343085627e79a4f13c29121646bb889cf901)
三表关联 怎么才能让这三个表都走hash连接呢，添加了hint 也不生效，那位大佬知道原因吗
obclient(root@ldf)[test]> explain
→ select * from sales a left join sa on sa.prod_id=a.prod_id and sa.c1=(select max(c1) from sb where sb.c1=a.c1);
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------- |
| |0 |NESTED-LOOP OUTER JOIN | |200000 |8082567306 | |
| |1 |├─PX COORDINATOR | |200000 |1021659 | |
| |2 |│ └─EXCHANGE OUT DISTR |:EX10000|200000 |718604 | |
| |3 |│ └─PX PARTITION ITERATOR | |200000 |36908 | |
| |4 |│ └─TABLE FULL SCAN |a |200000 |36908 | |
| |5 |└─SUBPLAN FILTER | |1 |40407 | |
| |6 | ├─DISTRIBUTED TABLE FULL SCAN|sa |1 |40361 | |
| |7 | └─SCALAR GROUP BY | |1 |47 | |
| |8 | └─TABLE FULL SCAN |sb |1 |47 | |
| ===================================================================

obclient(root@ldf)[test]> explain
→ select /*+ leading(a sa sb) use_hash(sa sb) */ * from sales a left join sa on sa.prod_id=a.prod_id and sa.c1=(select max(c1) from sb where sb.c1=a.c1);
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±---------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------- |
| |0 |NESTED-LOOP OUTER JOIN | |200000 |8082567306 | |
| |1 |├─PX COORDINATOR | |200000 |1021659 | |
| |2 |│ └─EXCHANGE OUT DISTR |:EX10000|200000 |718604 | |
| |3 |│ └─PX PARTITION ITERATOR | |200000 |36908 | |
| |4 |│ └─TABLE FULL SCAN |a |200000 |36908 | |
| |5 |└─SUBPLAN FILTER | |1 |40407 | |
| |6 | ├─DISTRIBUTED TABLE FULL SCAN|sa |1 |40361 | |
| |7 | └─SCALAR GROUP BY | |1 |47 | |
| |8 | └─TABLE FULL SCAN |sb |1 |47 | |
| ===================================================================

#3

这个位置 想走 hash 比nl好，

image
image1620×460 24.1 KB

#4

sales sa sb表结构 都是如下：
CREATE TABLE sales (
prod_id int(11) DEFAULT NULL,
price decimal(9,2) DEFAULT NULL,
c1 int(11) DEFAULT NULL,
c2 int(11) DEFAULT NULL,
c3 int(11) DEFAULT NULL,
c4 int(11) DEFAULT NULL,
c5 int(11) DEFAULT NULL,
saledate date DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by list(prod_id)
(partition p0 values in (1),
partition p1 values in (2),
partition p2 values in (3),
partition p3 values in (4),
partition p4 values in (5),
partition p5 values in (6),
partition p6 values in (7),
partition p7 values in (8),
partition p8 values in (9),
partition p9 values in (10))

#6

使用obdiag收集一下 plan_monitor
obdiag gather plan_monitor --trace_id YB420BA2D99B-0005EBBFC45D5A00-0-0 --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’}”
https://www.oceanbase.com/docs/common-obdiag-cn-1000000004494424