8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23 » Here

Materialized View Enhancements in Oracle Database 23ai

This post introduces some of the materialized view enhancements in Oracle database 23ai.

Related articles.

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 23ai 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 23ai 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 23ai.

Logical Partition Change Tracking (LPCT) - Staleness Tracking

In Oracle database 23ai 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.

Oracle 23ai introduces a number of enhancement for this functionality.

The documentation of these improvements is rather sparse, which is also true of all previous iterations of this functionality.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.