This article examines why a scalar subquery in an OceanBase 3.2.3.3 SQL statement takes over 1000 seconds, breaks down the execution plan, identifies the costly nested-loop behavior, and presents a rewrite using WITH and LEFT JOIN that reduces the cost from 788 million to 3.6 million and cuts runtime to about 10 seconds.
This article analyzes why a scalar subquery in an OceanBase 3.2.3.3 SQL statement runs over 1000 seconds, explains the execution plan details, and presents a rewrite using WITH and LEFT JOIN to replace SUBPLAN FILTER, reducing cost from 788 million to 3.6 million and execution time to 10 seconds.
This article explains the nature of scalar subqueries, identifies three common performance pitfalls, and provides practical rewrite techniques—including LEFT/INNER JOIN, MERGE, and WITH‑MATERIALIZE—illustrated with three real‑world Oracle SQL cases to dramatically reduce execution time.
A detailed Oracle case study shows how a seemingly harmless view modification triggered a cascade of performance regressions, how nested UNION ALLs, missing indexes, and optimizer choices prevented predicate pushdown, and the step‑by‑step rewrites—including hints, index creation, and scalar subqueries—that finally restored fast query execution.
