Re: Will simple view merging always be applied?

Hi William,
simple view merging is a heuristic transformation that is unconditionally applied when legal - that means no stale-stats can cause a change here (as it is not cost based). However if the subquery contains a GROUP BY and/or a DISTINCT, simple view merging can’t be used - complex view merging is an alternative here.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de
Twitter: _at_OracleSK

> William Robertson <william_at_williamrobertson.net> hat am 24.06.2024 01:44 CEST geschrieben:
>
> Hi all,
>
> We have a huge table with 200+ date partitions of 50 million rows each. Scanning the whole table would take hours so it's important to get partition pruning in every query.
>
> A simplified example like this reliably applies simple view merging and pushes the order_date predicate into the CTE and we only scan one partition. The actual code includes more joins, but the CTE still only contains one UNION ALL and no joins or aggregations, and it still works the same way in all tests (Oracle 12.2).
>
> My question is, how risky is this? Could some stale-stats issue arise in production causing it to scan every partition of huge_table twice before applying the date filter, or even try to materialise the CTE? I suppose I could add an /*+ inline */ hint to make sure that won't happen, but even then it might not apply the view merging.
>
> Is this a simple view in which merging always leads to a better plan?
>
> Thanks,
>
> William Robertson

--
http://www.freelists.org/webpage/oracle-l