Re: Will simple view merging always be applied?

From: William Robertson <william_at_williamrobertson.net>
Date: Mon, 24 Jun 2024 08:21:20 +0100
Message-Id: <DEC79D0B-EE76-466F-BF34-8797DDA9E2D0_at_williamrobertson.net>

Is it an optimiser decision though? The documentation suggests it might not be. Were partition views something the CBO had to think about at parse time, for example?

William

Sent from my iPhone

On 24 Jun 2024, at 05:13, Mark W. Farnham <mwf_at_rsiz.com> wrote:


I would remove the burden from the CBO to get it right by adding the and to each piece of the union all, AND I would replace the *s with the column names you are actually retrieving in the optimal retrieval order even if you re-order the columns in the second select.  

I don’t know how risky it is to let the CBO figure it out each time and possibly miss the pruning, but I know it is less work each time if it doesn’t have to figure it out.  

If you’re generating the query you could select from the partition name corresponding to the date used instead of the table, then the CBO doesn’t even need to do the pruning.  

I would think that usually the CBO would get the pruning right and I don’t have a catalog of when it might miss and not be considered a bug. I suppose if there is a global index on p_some_param and/or some_column it might not prune, especially if one of N, Y or one of the sets A,B or C,D is very sparse.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Robertson Sent: Sunday, June 23, 2024 7:45 PM
To: oracle-l
Subject: Will simple view merging always be applied?  

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

Received on Mon Jun 24 2024 - 09:21:20 CEST