Why Oracle’s Hidden Predicate Pushdown Alters SQL Plans – Lessons for Domestic Databases

Recent Oracle decisions to stop sales and services in Russia have sparked renewed interest in domestic database alternatives. The author argues that replacing foreign databases in critical systems is inevitable, but progress has been slow due to gaps in product capabilities.

These gaps are not merely technical; they span architecture, performance, and after‑sales support. Recognizing and addressing them is essential for domestic vendors to catch up with international leaders.

During a recent investigation, the author and a colleague discovered a puzzling case in a 100053 trace where Oracle automatically performed a predicate‑pushdown (FPD) transformation, adding a function‑based index predicate to the SQL. This rewrite changed the query semantics and produced incorrect results, initially appearing to be a bug.

Oracle later explained that the transformation is intentional, aimed at enhancing the Cost‑Based Optimizer (CBO). The author reproduced the scenario by refreshing the buffer cache and executing two similar queries—one with a condition on t_date_1 (which lacks a direct index) and another without that condition. The execution plans, shown in the images below, differ markedly.

The second plan uses an index named IDX_1 on a virtual column created as SUBSTR(T_DATE_1,1,10). Although the original WHERE clause had no index on T_DATE_1, the CBO added an implicit predicate AND SUBSTR("TEST_A"."T_DATE_1",1,8)='20220112', allowing the virtual column index to be used.

This demonstrates Oracle’s meticulous product engineering: by defining a clever FPD rule, the optimizer can exploit existing functionality without extensive redesign of the CBO. Such depth of design and the ability to capture rare use‑cases are valuable lessons for domestic database developers.

Another anecdote describes a customer’s database hanging issue. The author obtained a 200 MB Service Request (SR) report, which revealed that only about 10 % of the document contained actual problem analysis; the rest detailed internal diagnostics and collaborative solutions, highlighting Oracle’s rigorous post‑mortem process.

Comparing domestic vendors to foreign giants, the author notes that while product quality gaps are already large, the disparity in after‑sales support is even wider. Transforming support into a productive force for product improvement is crucial for gaining parity.

In summary, the two stories illustrate the importance of deep product knowledge, sophisticated optimizer techniques, and comprehensive customer‑centric support for advancing domestic database solutions.