Prior to OceanBase Database V4.0.0, due to the optimizer’s two-stage architecture, there were often issues with incorrectly selecting the distributed plan for join algorithms. In most scenarios, incorrect choices of nested loop joins or merge joins would result in slow SQL queries. In such cases, the DBA would need to manually instruct the optimizer to choose the hash join algorithm, which can resolve most slow SQL issues.
Another possible cause for slow SQL execution is inaccurate statistics. When the number of rows in the base table is seriously underestimated, the optimizer mistakenly believes that nested loop join is the optimal join algorithm. As a result, SQL execution becomes very slow due to quite a large amount of data in the driving table. In this case, the DBA also needs to manually instruct the optimizer to choose other join algorithms or change the join order. Generally, the problem can be solved by instructing the optimizer to use the hash join algorithm.