Re: Will simple view merging always be applied?
From: William Robertson <william_at_williamrobertson.net>
Date: Fri, 28 Jun 2024 08:14:01 +0100
Message-Id: <C9748D9D-9C52-434A-A480-844FEE622377_at_williamrobertson.net>
Date: Fri, 28 Jun 2024 08:14:01 +0100
Message-Id: <C9748D9D-9C52-434A-A480-844FEE622377_at_williamrobertson.net>
Are there any circumstances in which the optimiser would materialise a CTE that’s used only once, though?
On 27 Jun 2024, at 10:41, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
Interesting point about the /*+ inline */ hint placement - it is indeed ignored if it’s placed only in the topmost SELECT of the UNION ALL.
Thanks!
William
Sent from my iPhone
On 27 Jun 2024, at 10:41, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
As Stefan says, simple view merge is a heuristic (if you doubt that, try running an example using rule-based optimisation and you'll see that simple view merge still takes place - though in your case with partitioned tables the cost based optimiser will automatically take over and ignore the rule directive, so you'd need to construct a different example to test).
Your comment about materialization is a critical one, though, if the optimizer decides to materialize then it will do so BEFORE it applies the late predicate, so the /*+ inline */ hint might be a sensible strategic move. I think I've got a blog note somewhere that raises a concern about that when you have a union all view because you need to double-layer the CTE so that the hint can be in the right query block, viz:
with XXX as (
select /*+ inline */ columns
from (
select from t1 ....
union all
select from t2 ...
)
)
select ...
Regards
Jonathan Lewis
Regards
Jonathan Lewis
On Mon, 24 Jun 2024 at 00:45, William Robertson <william@williamrobertson.net> wrote:
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.In a PL/SQL procedure, I need to apply different filtering to huge_table depending on the value of parameter p_some_param. I could use an OR construction, or instead I can use something like this:insert into some_table (columns...)with filtered as
(select * from huge_table where p_some_param = 'Y' and some_column in ('A','B')
union all
select * from huge_table where p_some_param = 'N' and some_column in ('C','D')
)
select columns...
from filtered f
where f.order_date = date '2003-02-08';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.The docs say:"For certain simple views in which merging always leads to a better plan, the optimizer automatically merges the view without considering cost. Otherwise, the optimizer uses cost to make the determination. The optimizer may choose not to merge a view for many reasons, including cost or validity restrictions."Is this a simple view in which merging always leads to a better plan?Thanks,William Robertson
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 28 2024 - 09:14:01 CEST