Re: Will simple view merging always be applied?

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 ...
            )

Regards
Jonathan Lewis

Regards
Jonathan Lewis

On Mon, 24 Jun 2024 at 00:45, William Robertson < william_at_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."*
>
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/query-transformations.html
>
>
> Is this a simple view in which merging always leads to a better plan?
>
> Thanks,
>
> William Robertson
>
>
>

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