Enable query rewrite in an MV over a partitioned table
Oracle 12.2.0.1.0
I have a table (HDF) with 30 partitions, each partition has 4 subpartitions, with a record count of 434,555,225. A query to return the number of records in a partition should return 26,098,657.
select count(*) from RESDATA.HDF PARTITION (HDF_2019);
An MV depends on this table. The main table is updated nightly and its MVs refreshed afterwards. The architect for this system has retired, but I’m trying to understand the logic of enabling query rewrite.
If this MV has ENABLE QUERY REWRITE in its definition, the rows returned is 434,555,225 (total table count) instead of the partition count of 26,098,657. If I take ENABLE QUERY REWRITE