How I Cut a 2.5‑Second SQL Query to 1.8 Seconds: A Deep Dive into View‑Based Performance Fixes

1. The Trigger – A View Change Breaks Performance

After a recent view optimization, the BAS_PROJECT_ALL_V view (built with five UNION ALL blocks) was accessed three times in a 122‑line SQL statement, yet no predicate push‑down occurred, leading to a noticeable slowdown.

2. Urgency from Production

A production bug report highlighted that the same performance issue surfaced in the live environment, prompting an immediate on‑site investigation despite a seemingly generous deadline.

3. Business Requirement

The query must return, for a given user ID, all projects assigned to the user plus all initialized projects in the user’s region. The logical diagram (shown below) illustrates the two‑part result set.

4. Initial Analysis – Redundant Accesses and Cartesian Product

Key problems identified:

Multiple tables were accessed repeatedly (highlighted in yellow and blue in the view diagram).

Several LEFT JOIN operations fetched columns that the outer query never used, making them unnecessary.

The sub‑query SUROT_T lacked join conditions, causing a Cartesian product.

Attempting predicate push‑down did not improve the plan, so a deeper rewrite was required.

5. Major Rewrite – CTEs and EXISTS

The solution involved four main changes:

Replace the view BAS_PROJECT_ALL_V with a WITH common‑table‑expression that only includes needed tables.

Convert the problematic LEFT JOIN … WHERE pattern into an EXISTS sub‑query.

Defer the ROUTE and T sub‑queries until after the project list is assembled, then join to fetch attribute fields.

Remove duplicated table accesses and unnecessary joins inside the CTEs.

After the rewrite, the execution plan (images below) showed a drastic reduction in cost.

Running the query in PL/SQL DEV now took about 2.5 seconds, a clear improvement.

6. Fine‑Tuning – Divide and Conquer

Further analysis revealed that the six UNION ALL sources in the CTE generated ~31 MB of I/O for only ~1 000 result rows. By splitting the combined CTE into separate parts (“divide and conquer”), the I/O dropped dramatically.

The revised query (shown in the attached images) achieved a runtime of 1.8 seconds, meeting the stricter 2‑second SLA.

7. Takeaways

The case demonstrates that a “good” SQL is one that is simple in structure and logic, not merely short in line count. Eliminating unnecessary joins, pushing predicates, and using CTEs to isolate logical steps often yield larger gains than hints or index tweaks.