My Oracle Support Banner

Use of SYS_OP_MAP_NONULL() May Not Be Needed in Fast Refresh When Group By Column Is NOT NULL (Doc ID 1941488.1)

Last updated on DECEMBER 17, 2014

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.2 to 12.1]
Information in this document applies to any platform.


In the refresh (INS) DML statement (used when the refresh contains mixed DML),  SYS_OP_MAP_NONNULL("MAS$"."<groupby_column>") is referenced in the WHERE clause.  The regular index on this column does not get used.

Under the following conditions, we would expect to see nested loops driven by the mvlog rows use the index on the GROUP BY column to access the master table for the fast refresh.  Instead we see a FTS of the master table.

-- The GROUP BY column of a fast-refresh, aggregated mview has as a NOT NULL constraint for that column in the master table.   
-- There is a single-column, non-unique index on the GROUP BY column in the master table. 
-- There are only a few rows in the mvlog to apply. 
-- Stats are current. 



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.