Re: Priority of profile baseline patch

>> I just fetched the explain plan by using ' autotrace traceonly explain'
command and I see that there exists multiple UNION ALL must be because of the existing unusable index partition(~94 out of total 442). But along with that I also noticed the table partitions(mainly historical partitions) are compressed using 'archive high'. ~388 are compressed and ~54 partitions are non compressed. Will that also play a role in such a table expansion plan?

I don't think there's any definitive statement from Oracle about when table expansion could occur, or how it is implemented, so it's possible that ANY variation in the infrastructure of different partitions MIGHT prompt table expansion, and it's possible that table expansion occurs BEFORE access path analysis, in which case you MIGHT see exactly the same path in every single branch of the expansion. The only way to find out is to construct a series of experiments.

Possible features to test for table expansion.

Partial indexing
Unusable index partitions
Different levels of compression in table partitions Different levels of compression in index partitions Differences of in-memory specification for different partitions.

Then you may find that pure range partitioning behaves differently from interval partitioning
And list partitioning may behave differently from range or interval partitionining
And composite or reference partitionining may have implemented only a limited subset of the table explansion features.

A little light reading, though: here's a note I wrote about some testing that started in 12.1.0.2 (and immediately found a "wronge results" bug in table expansion) and then goes on to test a few more variations of things that might affect table expansion and how the optimizer might respond: https://jonathanlewis.wordpress.com/2015/11/24/table-expansion/

Regards
Jonathan Lewis

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