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

Unexpected Performance Drop After a View Change

During a late‑afternoon email titled “B3‑SQL query slow issue”, the author discovered that a recent modification to the view BAS_PROJECT_ALL_V caused a noticeable slowdown. The DBA supplied execution plans before and after the change, highlighting a shift from nested loop joins to a hash join and the loss of predicate pushdown.

Initial Diagnosis and Hint Attempt

The author first tried to force predicate pushdown with the hint /*+ push_pred(p) */, which restored sub‑second performance. However, developers rejected hint‑based solutions because the view is a foundational object accessed by thousands of SQL statements, making per‑statement hints impractical.

Root Cause: UNION ALL and Missing Index

Further investigation revealed that the view incorporated a UNION ALL of the same table BAS_PRM_OPPORTUNITY_T twice, effectively duplicating the result set. Although the table contained only about 7,000 rows, it lacked an index on the join column SF_OPPORTUNITY_CODE, preventing the optimizer from considering predicate pushdown as a profitable path.

To address the missing index, the following statements were executed:

CREATE INDEX ind_BPO_n1 ON BAS_PRM_OPPORTUNITY_T(SF_OPPORTUNITY_CODE);
CREATE INDEX ind_BPO_n2 ON BAS_PRM_OPPORTUNITY_T(SF_OPPORTUNITY_CODE||'V');

Even after index creation and statistics refresh, Oracle still refused to push the predicate, indicating a deeper optimizer interference.

Optimizer Interference by Nested UNION ALL

Analysis of the execution plan showed that Oracle extracted the BAS_PRM_OPPORTUNITY_T UNION ALL block into a separate view, then performed left joins with three other tables ( BAS_HR_AREA_ORG_HIERARCHY_T, SDCP_PROJECT_ROUTE_T, etc.). This transformation reduced the number of accesses to those tables but also prevented predicate pushdown, effectively “helping” the query in a counter‑productive way.

By commenting out one side of the UNION ALL, the plan reverted to using the index and predicate pushdown, confirming that the nested UNION ALL was the culprit.

Rewriting the View to Eliminate UNION ALL Interference

The author extracted the nested UNION ALLs to the outermost level, simplifying the view logic. After recompiling the view, the execution plan once again showed predicate pushdown.

However, the problem resurfaced the next day when another UNION ALL appeared inside the BAS_PRM_OPPORTUNITY_T integration block. The view code had not been overwritten, indicating that the optimizer was re‑introducing the nested structure during its rewrite.

Using Scalar Subqueries to Bypass Optimizer Rewrite

To prevent the optimizer from flattening the joins, the author replaced the three left‑joined tables with scalar subqueries, justified by three observations:

Each join column is a unique key, guaranteeing a single result row.

All joins are left joins, so converting to scalar subqueries does not change result cardinality.

The view only selects one or two columns from each table, avoiding a proliferation of scalar subqueries.

After applying scalar subqueries and recompiling, the query again achieved predicate pushdown and restored fast execution.

Key Takeaways

Large or deep views often contain unnecessary tables, columns, or UNION ALLs that degrade performance.

Missing indexes on join columns can cause the optimizer to skip predicate pushdown.

Hints can be a quick fix but are rarely acceptable in production when the view is widely used.

Rewriting views to remove nested UNION ALLs or to use scalar subqueries can force the optimizer to generate efficient plans.

Continuous monitoring of execution plans is essential after any view modification.

Images illustrating the original and modified execution plans, view code snippets, and index creation scripts are retained below for reference.