RE: Will simple view merging always be applied?

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

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/querytransformations. 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