BestHub
Sign in
Tagged articles
4 articles
Page 1 of 1
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 25, 2024 · Databases

Analyzing and Optimizing Slow Scalar Subqueries in OceanBase 3.2.3.3

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.

OceanBasePerformance OptimizationSQL
0 likes · 12 min read
Analyzing and Optimizing Slow Scalar Subqueries in OceanBase 3.2.3.3
dbaplus Community
dbaplus Community
Mar 4, 2020 · Databases

How to Optimize Scalar Subqueries in Oracle SQL for Better Performance

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.

OracleSQLScalar Subquery
0 likes · 15 min read
How to Optimize Scalar Subqueries in Oracle SQL for Better Performance
dbaplus Community
dbaplus Community
Nov 30, 2016 · Databases

Why Updating a View Can Kill Oracle Performance—and How to Fix It

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.

OraclePredicate PushdownSQL
0 likes · 13 min read
Why Updating a View Can Kill Oracle Performance—and How to Fix It