Why a Single Filter Turned a 2‑second Oracle Query into 90 Seconds—and How to Fix It

After publishing the article "SQL Optimization Case Five Flavors," the author was confronted by a developer who complained that a newly added filter condition had dramatically slowed the query. The original query ran in about 2 seconds; with the extra condition AND (T1.TASKLOWIDS IN (18061000)) it took 90 seconds.

Initial Diagnosis

The author suspected a change in the execution plan. By comparing the two plans, the following differences were observed:

Without the new filter, the driving table is RP_PLAN_LOG_T. With the filter, the driving table becomes SDS_DU_TF_RELEASE_T.

The join method changes from NESTED LOOPS to HASH JOIN .

The access path for SDS_DU_TF_RELEASE_T switches to TABLE ACCESS BY INDEX ROWID because the filter creates an index on TASK_FLOW_ID.

Why the Filter Affects the Plan

The added condition targets TASKLOWIDS, which is indexed. Oracle assumes the filter will significantly reduce rows, so it promotes SDS_DU_TF_RELEASE_T to the driving table and chooses a hash join, which is far more expensive for this data set.

General Remedy

Typical solutions include using optimizer hints ( /*+ LEADING ... */), disabling the unwanted index with NO_INDEX, or forcing a nested‑loop join with USE_NL. These techniques can steer the optimizer back to the original, efficient plan.

Deeper Investigation: Why RP_PLAN_LOG_T as Driver Is Faster

The author simplified the query to focus on RP_PLAN_LOG_T and changed the ORDER BY column from OPERATE_TIME to CDESCRIPTOIN. With the original order, the plan used INDEX RANGE SCAN followed by SORT ORDER BY STOPKEY, costing 1910 units. After switching the order column, the sort step disappeared, reducing the cost to 11 and execution time to milliseconds.

When ordering by a column that has a (prefix) index and the table is the driving table, Oracle can return rows already sorted, eliminating the separate sort operation. However, this only works if two conditions are met:

The ordering column must have an index.

The table containing the ordering column must be the driving table in the plan.

Case Study: Misleading Index on PLAN_LOG_ID

Replacing ORDER BY OPERATE_TIME with ORDER BY PLAN_LOG_ID (a unique indexed column) removed the sort step, but runtime ballooned to 66 seconds because the optimizer still fetched ~45 MB of data, effectively performing a full table scan.

Key Takeaways

Even a single additional filter can trigger a cascade of plan changes, especially when it introduces a new index.

Understanding which table drives the plan and how join methods are chosen is crucial for performance tuning.

Proper use of hints or index management can prevent unwanted plan shifts.

Sorting on an indexed column is only beneficial when that table is the driver; otherwise, the optimizer may still need to sort large result sets.

By analyzing execution plans, monitoring actual I/O, and applying targeted hints, the author restored the query to its original performance and clarified why the optimizer behaved as it did.