Will simple view merging always be applied?

From: William Robertson <william_at_williamrobertson.net>
Date: Mon, 24 Jun 2024 00:44:55 +0100
Message-Id: <26634B89-0FC2-406D-BA01-3C13A26413DE_at_williamrobertson.net>

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

Received on Mon Jun 24 2024 - 01:44:55 CEST