Re: Will simple view merging always be applied?

Hi,

there are no guarantees in these cases.
Your query resembles a partitioned view which is a well tried construct. You should be fairly save that will work as desired. You might want to use plan stability
(https://blogs.oracle.com/optimizer/post/oracle-database-19c-and-sql-plan-management-diagnostics).

Thanks

Lothar

Am 24.06.2024 um 01:44 schrieb William Robertson:
> 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