8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Materialized View Enhancements in Oracle Database 23c
This post introduces some of the materialized view enhancements in Oracle database 23c.
- Concurrent Refreshes
- Support for ANSI Joins
- Semi-Join Materialized View Rewrites
- Logical Partition Change Tracking (LPCT) - Staleness Tracking
- Extended Support for JSON
Related articles.
- Materialized View Concurrent Refreshes in Oracle Database 23c
- Materialized View Support for ANSI Joins in Oracle Database 23c
- Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
- JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
- Materialized View Support for Queries Containing JSON_TABLE in Oracle Database 19c
Concurrent Refreshes
In previous releases on-commit materialized view refreshes were serialized. If multiple session triggered an on-commit refresh of the same materialized view, they were performed one at a time. This could impact performance on busy tables. In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.
Support for ANSI Joins
In Oracle 23c materialized views support query rewrites for SQL statements using ANSI or Oracle style joins. This means your materialized view definitions and SQL statements can use either join syntax and still allow query rewrites.
Semi-Join Materialized View Rewrites
Semi-Join Materialized View Rewrite is a unique form of query rewrite introduced in Oracle Database 23c.
Logical Partition Change Tracking (LPCT) - Staleness Tracking
In Oracle database 23c Logical Partition Change Tracking (LPCT) allows materialized view staleness to be tracked at the level of a logical partition.
Extended Support for JSON
Oracle 18c introduced the ability to use the ON STATEMENT refreshes of materialized views built with JSON_TABLE function calls. Oracle 19c can perform query rewrites of statements using some SQL/JSON functions (JSON_VALUE, JSON_EXISTS) to use a materialized view containing an appropriate JSON_TABLE call.
- JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
- Materialized View Support for Queries Containing JSON_TABLE in Oracle Database 19c
Oracle 23c introduces a number of enhancement for this functionality.
- Fast refreshes are supported on a wider variety of
JSON_TABLEmaterialized views. - Fast refreshes support multi-table materialized join views (MJVs) and materialized aggregate views (MAVs). In the previous release we were limited to a single table materialized join views.
- Query rewrite support has been improved to support the new variations described above.
The documentation of these improvements is rather sparse, which is also true of all previous iterations of this functionality.
For more information see:
- Materialized View Concurrent Refreshes in Oracle Database 23c
- Materialized View Support for ANSI Joins in Oracle Database 23c
- Logical Partition Change Tracking (LPCT) for Materialized Views in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
- JSON_TABLE Materialized Views : ON STATEMENT Support in Oracle Database 18c
- Materialized View Support for Queries Containing JSON_TABLE in Oracle Database 19c
Hope this helps. Regards Tim...