Database Administration (MOSC)

MOSC Banner

Enable query rewrite in an MV over a partitioned table

edited May 11, 2020 10:40AM in Database Administration (MOSC) 9 commentsAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center