We have 2 materialized views which are basically the same but
partitioned differently. Reports are either by posted date or
transaction date. We created the two separate materialized views in
hopes that Oracle would choose the appropriate materialized view at
query time dependent on if the query written against the base tables was by posted date or transaction
date to take advantage of partition pruning.
In testing this I have found this not to be the case. The query in
question is based on posted date however Oracle is choosing to use the
materialized view partitioned by transaction date. When using the
rewrite hint and hinting the materialized view on posted date the
query has a much smaller cost, prunes as expected, and resolves much
faster.